Incremental on chart

Hello Team,

Need a small help.
I wish to create a chart showing my annual spend. This is quite simple :slightly_smiling_face:.

Now on the same chart/ table, I wish to add

  1. a number/line chart which tells me from the one year to the next year how much my spend is increased/decreased (in absolute amount and in % as well).
  2. Add another line chart showing the % difference in a given year compared to the amount spend in the year with the minimum fiscal spend.

Any suggestions on how I can achieve it?

Regards,
Sachin

Hi,

1a) In order to calculate the difference between the total spend in current year to the one immediately after it I would use the Moving average function in the following way:

"MOVING_AVG ( SUM(“table”.“spend”), 1, 1 ) - MOVING_AVG (SUM(“table”.“spend”), 0, 0 ) "

Note: the two numbers in MOVING_AVG denote the which rows in relation to the current row should be averaged. It’s easier to picture if you consider that every chart in Celonis is an OLAP table presented in a different way. In the first use of the function ‘1,1’ is written this means that the start and end of the range of values being averaged are both the row after the current row, so as there is only one value in the range in effect no averaging has taken place. It’s a neat trick for getting hold of the value in next row so that the current one can be subtracted from it to find the difference.

1b) To get this difference as a percentage: make another KPI, with a name like ‘% difference spent’ with the same code as above. Then in the drop down at the top of the component options side bar select “Data series: % difference spent”. Then click the box next to “Calculate share in percentages”, this will make a new formula box appear. In it just write “SUM(“table”.“spend”)”. This will mean the KPI from 1a) is now divided by the spending in the current year/row, giving the relative rather than absolute increase. I would suggest moving this KPI to a secondary axis. You can also make it a line chart by selecting “Line chart” in the drop down next to “Alternative Type”.

2 ) To get the % difference in spending compared to the minimum year. I would suggest just manually identifying the minimum year and then using a CASE WHEN statement to sum up all the spending in all cases in that year to get the spending in the minimum year and just dividing the total spent by this value.

SUM(“table”.“spend”) / GLOBAL (SUM(CASE WHEN YEAR(“activity table”.“EVENTTIME”) = XXXX year THEN “table”.“spend” ELSE NULL END))

Where instead of XXXX write your minimum year.

Note: The function GLOBAL is essential because it means that the aggregation is made over all cases and not just the ones in the current row (which is in this case those belonging to a particular year).

I hope this helps, if not please write back and we’ll try to help you further.

best wishes,

Calandra (Celonis Data Science Team)

1 Like

Hi Calandra,

You are awesome. :slight_smile: :+1:

Thanks a million.

Regards,
Sachin