Open invoice aging trend over time in a line chart

Question

Hi celopeers,

I am trying to build out a trend chart that plots the open invoice aging over time.

In terms of dimensions I have the following:

x axis - ROUND_MONTH(INVOICE CREATION DATE)

y axis - AVG(TODAY - CREATION DATE) for open invoices only

The issue I'm running into is that since KPI is tied to creation date and today's date produces a strictly negatively sloped trend as shown below, which makes sense in practice (beacuse both axes are tied to creation date), but not exactly what I'm looking for.

What I need to show is the change in trend by month, but that will require detaching the KPI calculation from the creation date, sort of like a snapshot of the KPI at the end of each month, kind of like transformation center did it before. Has anyone successfully accomplished that before? If so - how did you do that?

Thanks!

Eugene

burim.hoxha · Answer

Hello Eugene,

thank you for the question.

To break the relationship between the KPI and the creation date, we can use RANGE_APPEND on the x-axis like so:

RANGE_APPEND(ROUND_MONTH("ACTIVITY_TABLE"."EVENTTIME"), '1M')

Then we can compute a KPI, e.g. the number of open invoices in any given month by using a RUNNING_SUM. Here, we assume that each invoice is only created and cleared once:

RUNNING_SUM(

2SUM(

3CASE WHEN "ACTIVITY_TABLE"."ACTIVITY_EN" = 'Create Invoice' THEN 1

4WHEN "ACTIVITY_TABLE"."ACTIVITY_EN" = 'Clear Invoice' THEN -1

5ELSE 0

6END

7), ORDER BY (MIN("ACTIVITY_TABLE"."EVENTTIME"))

8)

This is also documented here (you should have access to this): https://pql.eu-1.celonis.cloud/process-mining/public/105139f8-2fa6-4ef2-b2e0-c33299e3f7e7/#/frontend/documents/105139f8-2fa6-4ef2-b2e0-c33299e3f7e7/view/sheets/17480c67-60f9-461e-a1c6-fc24a70ce20f

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN