Skip to main content
Hi Celonis Community,
Ive recently started working in PM and am faced with my first real challenges.
To answer complex questions of the process owner I would need to execute more complex calculations in Celonis. Im unsure to which extent this is supported.
Ive read through the functions documented in the help section and functions highlighted here but could not find any that provide what Im looking for.
Is it possible to base a calculation in Celonis on a previously calculated column? An equivalent of what Im trying to achieve might be subqueries in SQL.
Im unable to go give insight into the process and will provide a much simplified example.
My activity table looks like this:

grafik.png886138 2.98 KB


Based on this I can calculate a new table with the automation rate per CASE


In the following I want to use the calculated column, to base further calculations on it. Like:

OR

I hope this is understandable enough. Im also positive, that there might be an easier way to achieve this, but I am curious, wether a powerful construct like subqueries in SQL exist in celonis.
My first idea was, that these kinds of calculations would be possible, if new calculations could be based on OLAP tables created in the analysis.
Best,
David
Hi David,
You can calculate something like that using PU functions:

PU_AVG(CASE_TABLE, CASE WHEN ACTIVITY_TABLE.Automated=Yes THEN 1.0 ELSE 0 END)

Please note that I am assuming the names for your case and activity table. PU_AVG will give you a column which can be used as a dimension and from which you can calculate an average.
PU functions are more advanced language elements. I highly recommend you to read the following post: The longest but ultimate guide for being a Pull-Up-Functions expert
Best regards
Pol
Hi Pol,
thank you for your help! Ive read through the doumentation of the PU functions and the feature of the week post yesterday but could not come to a solution myself. So your reply is very helpful.
Previously, I had calculated the same column by declaring the CASE ID as the dimension and using

AVG(CASE WHEN ACTIVITY_TABLE.Automated=Yes THEN 1.0 ELSE 0 END)

I have some questions about the result of the PU function.
  • How can I use this resulting column as a basis for further calculations?
    • Knowing the average automation for every case, how can I calculate the average automation rate of cases sharing a certain characteristic? (Picture 3 and 4 in the post)


Would this be first use case of the PU function guide? Would this be possible by using a Domain table to add the needed aggregation layer?
  • In my original calculation I could ignore certain activities with a filter. The PU function ignores this filter and considers all activities.
    • Is it possible to filter the activities being considered in a PU function?


In the PU function GUIDE v.kalversberg mentions that PU functions ignore filters. Is it possible to still create the same behaviour ?
Best regards,
David
Hi David,
In your example something like the following would be possible:

Dimension:
Characteristic
KPI:
AVG(PU_AVG(CASE_TABLE, CASE WHEN ACTIVITY_TABLE.Automated=Yes THEN 1.0 ELSE 0 END))

PU functions have an optional third parameter to provide filters, e.g. you only want to consider Activity A1:

PU_AVG(CASE_TABLE, CASE WHEN ACTIVITY_TABLE.Automated=Yes THEN 1.0 ELSE 0 END, ACTIVITY_TABLE.Activity=A1)

Yes it would correspond to the first example of the guide. Domain tables cannot be used to add another aggregation layer.
Best regards,
Pol
Hi Pol,
thanks for sticking with me. Ive followed your instructions and created a table that looks like this:

Im still unsure of how far I can push this.
Could I now work further with this calculated table, e.g. base other calculations on it? As an example, could I calculate an average per Characteristic of the calculated table shown above?
From the guide Ive gathered that its possible to base filters on PU functions.
To add a little context: In my current project Im working with a single file and am trying to find out how much insights I can extract from this in Celonis. The data is not in the ideal shape for process mining.
Best,
David
Hi David,
Yes you can calculate an average, thats exactly what Ive show in the example in my previous post. Please note that PU_AVG is within a normal AVG.
Best regards,
Pol
Hi Pol,
my bad. I did indeed miss the outer AVG.
Thanks a lot for your help with this. The PU functions are a lot more powerful than I realized.
Best,
David

Reply


Terms & ConditionsCookie settings