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

Question

Dear all,I have a time series plot of the automation rate like the following: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? In the end I want to have a list of KPIs which from design point of view look like the followingIs 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

david.beche12 · Answer

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 toAVG(KPI("Automation")).Let me know if you encounter any problems!BestDavid

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