Chaining PQL Queries

Hi Celonis Community,

I’ve 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. I’m unsure to which extent this is supported.
I’ve read through the functions documented in the help section and functions highlighted here but could not find any that provide what I’m looking for.

Is it possible to base a calculation in Celonis on a previously calculated column? An equivalent of what I’m trying to achieve might be subqueries in SQL.

I’m unable to go give insight into the process and will provide a much simplified example.
My activity table looks like this:


grafik
Based on this I can calculate a new table with the automation rate per CASE
grafik
grafik
In the following I want to use the calculated column, to base further calculations on it. Like:
grafik
OR
grafik

I hope this is understandable enough. I’m 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! I’ve 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

1 Like

Hi Pol,

thanks for sticking with me. I’ve followed your instructions and created a table that looks like this:
grafik
I’m 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 I’ve gathered that it’s possible to base filters on PU functions.

To add a little context: In my current project I’m 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, that’s exactly what I’ve 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