Hello, I am wondering if there is a capability similar to Excel’s pivot charts, where one can break a standard column chart further by another dimension, creating multiple series.
Splitting a Column Chart by another dimension is straightforward, just add another dimension. However, it should be noted that in Celonis instead of the new dimension being written on the side of the plot it is added to the axis names connected by a dash.
The greater challenge in reproducing the excel chart lies in grouping the dates and getting the colour scheme to work.
To show you what the Celonis version of the table would look like, I made a mock-up of your chart using demo data:
To gather your dates in to three groups making a dimension with this code should do the trick:
DAYS_BETWEEN(TO_TIMESTAMP(‘08.07.2009’, ‘MM.DD.YYYY’), “your table”.“your date variable” <= 0
THEN ‘8/1/2019 - 8/7/2019’
WHEN DAYS_BETWEEN(TO_TIMESTAMP(‘08.14.2009’, ‘MM.DD.YYYY’), “your table”.“your date variable”) <= 0
THEN ‘8/8/2019 - 8/14/2019’
ELSE ‘8/15/2019 - 8/16/2019’
To explain: DAYS_BETWEEN() returns the integer of numbers of days between the first timestamp given to it and the second. So, if the second date comes earlier the value returned is negative. In case when statements the first true condition is used so for weeks in the middle bracket it suffices to ask whether it comes before the upper limit because if it came before 8/8/2019 it would have already been assigned the first value.
To get the columns colour coded by plant:
The palette colours are set in the tab: “Data series: YOUR KPI NAME”, you can get there using the dropdown menu under “component options”
make a pallet with the same number of columns as plants (in case where the number of columns exceeds the number of colours in the pallet it cycles round and uses the colours again in order starting from the beginning).
It may be necessary to select “Palette colours based on sorting” to ensure the colours are assigned in the correct order.
I hope this answers your question. Feel free to make a new post if you ever need our help again