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

1 Like

Hi Calandra,

Is it possible to created incremental chart for few of dimensions, like this.

We’ve one component like this:
image

And what we want to generate is following:
image

Basically incremental for dimension #4 and #5.

Is there any workaround we can achieve this. :wink:

KR,
Kalpesh

Hi @KalpeshYogi,

The thing is if you use a case when statement like you presumable do in your dimension, then each case is only counted in one ‘When’ statement. The only way to make the same case get counted in multiple rows is to use RUNNING_TOTAL. The next issue is that RUNNING_TOTAL has to be applied to all rows; it is not possible to write a case when statement that using RUNNING_TOTAL on some rows and just gives the count in others but the good news is I’ve found you a workaround.

My Idea:

So, my idea is to use two KPIs. In one of them Running total is used for <30 Days, <90 Days and <180 Days and for all other categories it just returns a 0. In the other KPI the Case Count (or whatever your counting in your table) is given for the other categories and 0 for the ones on which you want to use the running total. Then in the plot you stack the two KPIs and give them the same colour to give the illusion of them being the one column (which would have the desired height).

For technical reasons, to use this solution, you would need to change the order of your columns so that the running total columns are at the end. So “6. >180 Days” would have to be “3. >180 Days”. Sorry about that :confused:

Now for a technical explanation of how to implement it.

Make a Column Chart and add:

  • The dimension is the same as in your original table (which I assume is a CASE WHEN statement).
  • Make a KPI with the code:

PU_COUNT_DISTINCT( DOMAIN_TABLE (), “ACTIVITIY TABLE “.”_CASE_KEY”, NOT(“TABLE”.“DAY VARIABLE” < 180))

This will give you case count for every category other than <30 Days, <90 Days and <180 Days. If you aren’t using case count, then instead of “ACTIVITIY TABLE “.”_CASE_KEY” concatenate all the important columns of the table your counting so that you have a unique identifier for what your counting. Concatenating columns uses the following syntax: “table”.”column1” || “table”.”column2” || “table”.”column3”.

Make another KPI with the code:

RUNNING_TOTAL(PU_COUNT_DISTINCT( DOMAIN_TABLE (), “ACTIVITIY TABLE “.”_CASE_KEY”, “TABLE”.“DAY VARIABLE” < 180))

The running total will be formed over the case count. Due to the filter on “TABLE”.“DAY VARIABLE” < 180 the rows other than <30 Days, <90 Days and <180 Days will have a count of 0. However, if “>180 Days” comes after “<180” it would have the same value as “<180”, which is why the category needs to be moved.

Choose the option “Stacked (on Primary Axis)” and give it the same colour as the previous KPI.

Add the KPI: COUNT_TABLE(“CASE TABLE”) (or what every our counting)

This KPI is needed because otherwise for complicated technical reasons the plot won’t have one bar per Category.

You don’t actually want to see this KPI so it can be hidden by setting “Series Color” to transparent. If you also chose “Stacked (on Primary Axis)” then the columns are no further apart than before. It would however increase the range of the axis, if that’s a problem you can manually change the Axis Range by selecting “Manual-Range” in “Primary Value Axis” options.

Hide all evidence of this sneaky work around by hiding the legend (option is found in 'Diagram Area & Legend Options). Just remember to make it clear in the title of the component what’s being counted.

I hope this helps. If you have any further issues, then please write back.

Kind Regards,

Calandra