Dear fellow process miners,
I’m currently struggling implementing a normalization of a result in a table. It seems to be impossible to achieve that an OLAP table in Celonis.
Here’s my problem:
Every case has a list of 0 to N events that affect the duration of the case. It’s a simple parent-child relationship in the data model. If no event happened to a case, there will be no record in the child table.
Now I created a simple OLAP table with the event category (event.category) as dimension.
My two KPI columns so far are
- The number of events of that category as
- The sum of delay in the process, caused by that event as
Now, I want to normalize the delay to 100 total cases. Say, I have 15 events, causing 15 minutes of delay. In total, I have 250 cases. What I’m trying to achieve is the following simple formula:
(SUM(delay)/Total number of cases)*100, which should be (15/250)*100 = 6.
However, this seems to be impossible to achieve in Celonis as
- GLOBAL() will only give me the number of cases shown in the table, which will exclude the 235 cases having no events (As no column of the case table is involved as dimension)
- A static variable will not be filtered with selections
- A PU-function will not work, because it will also not include the 235 cases without an event.
At the moment, I don’t see a chance to achieve such calculation due to the above limitations.
Did any fellow process miner stumble upon such thing and find a solution to it? The cherry on the cake would be to apply a filtering on the number of cases (e.g. an attribute on the case table) for the normalization (e.g. with a case when statement)
Any help would be greatly appreciated.