Getting Difference in consecutive data values in time series data (column chart)

#1

Dear all,

I have a time series plot of the automation rate like the following:

image

  • x- Axis: ROUND_MONTH(“EKKO”.“AEDAT”)

  • y-Axis: KPI(“Automation Rate”) (standard automation rate formula; should work then with any KPI)

My Goal is to calculate the difference in the automation rate between the current month and the previous one i.e. get as result 4.8%.

How to do this in celonis? :slight_smile:

In the end I want to have a list of KPI’s which from design point of view look like the following

image

Is there a simple way to calculate the 4.8% in a dynamic way (the 7.33% is coming from 2 years of data)?

Thxs & Br,
Stefan

0 Likes

#2

Hi Stefan,

welcome to the Celonis Community!

You can calculate the number by setting all values belonging to dates which are not in the months you want to compare to NULL.
The formula would look like this:

AVG( CASE WHEN ROUND_MONTH("EKKO"."AEDAT")=ROUND_MONTH(TODAY()) THEN KPI("Automation") ELSE NULL END)-
AVG( CASE WHEN ROUND_MONTH("EKKO"."AEDAT")=ADD_MONTHS(ROUND_MONTH(TODAY()), -1) THEN KPI("Automation") ELSE NULL END)

Please note: Your Saved formula (in this example the Automation Rate formula) has an Aggregation function inside (in your case an AVG). In my proposed solution, you need to use a saved formula without this aggregation function. So what you could do is to create another saved formula called “Automation” which contains your automation rate formula without the AVG wrapped around.
To get rid of duplicate code, you can then rewrite the “Automation Rate” KPI to
AVG(KPI("Automation")).

Let me know if you encounter any problems!

Best
David

0 Likes

#3

Dear David,

thank you for the quick reply.

It works like a charm! :slight_smile:

Br,
Stefan

1 Like