Finding the interquartile range

Hi, does anybody know how to get the interquartile range, Q1 and Q3 when using the median?
I am using the median from two places in Celonis. Firstly the median case duration which I got from the Process Overview on the Throughput Times tab by changing the ‘Average Throughput time’ to ‘Median Throughput time’. Secondly I am reporting the median number of days between events from within the process explorer, displaying Throughput Time (Median). I know it must be available, I’ve just no idea how to get it. Thanks in advance to anybody who can help!

you can define your own process KPIs. Go to Analysis settings -> Process Explorer KPIs -> Create KPI. There you can define the KPI you would like to display next to each activity and (when you switch to the tab Connection KPIs below the icons), the KPI you would like to diplsplay next to each link. For the IQR choose “New connection KPI” and enter
QUANTILE(1.0 * DATEDIFF(dd, SOURCE(“YourActivityTable”.“YourTimestamp”), TARGET(“YourActivityTable”.“YourTimestamp”)),0.75)-(1.0 * DATEDIFF(dd, SOURCE(“YourActivityTable”.“YourTimestamp”), TARGET(“YourActivityTable”.“YourTimestamp”)), 0.25)
if you want the median next to it, click again on “New connection KPI” and enter
MEDIAN(1.0 * DATEDIFF(dd, SOURCE(“YourActivityTable”.“YourTimestamp”),TARGET(“YourActivityTable”.“YourTimestamp”))).
Best, Stephanie

Hi Stephanie,
Thank you for your help! I have followed your instructions and entered:
QUANTILE(1.0 * DATEDIFF(dd, SOURCE(“Ex15_PWBack_female”.“eventDate”), TARGET(“Ex15_PWBack_female”.“eventDate”)),0.75)-(1.0 * DATEDIFF(dd, SOURCE(“Ex15_PWBack_female”.“eventDate”), TARGET(“Ex15_PWBack_female”.“eventDate”)), 0.25).
I then go into my process explorer and my new KPI is available to select, though when I select it I get the message: “The custom KPI view could not be applied. Check your Connection KPI formula”.
I am sure that what you have told me is correct and it is something that I am doing that is wrong. Any ideas? Thank you Sam

sorry the second QUANTILE was missing from the formula (bold in the code below)! I hope it works now :slight_smile:

SOURCE(Ex15_PWBack_female.eventDate), TARGET(Ex15_PWBack_female.eventDate)
), 0.75) -
SOURCE(Ex15_PWBack_female.eventDate), TARGET(Ex15_PWBack_female.eventDate)
), 0.25)


PS: please also note that the quotation marks were wrongly formatted in my first post (default by the text editor used in this forum) - they should always be straight. You can either leave them out as in this post or replace them again in the formula editor in Celonis to make sure that they have the right format!

Hi Stephanie,
thank you again for your reply. I have typed in the following:
QUANTILE(1.0 * DATEDIFF(dd, SOURCE(“Ex15_PWBack_female”.“eventDate”), TARGET(“Ex15_PWBack_female”.“eventDate”)),0.75)-QUANTILE(1.0 * DATEDIFF(dd, SOURCE(“Ex15_PWBack_female”.“eventDate”), TARGET(“Ex15_PWBack_female”.“eventDate”)), 0.25)
I have tried it also without the quotation marks, tried it with event log name all capitals (long shot but just in case) but I’m still coming up with the same error message. Any more ideas?
Thank you! Sam

Hi Sam,
unfortunately not. I have tried it and it works fine for me.
I have defined the Median first

then the IQR

and get the following picture in the process explorer:
As mentioned in the screenshot, you can try to add the source and target with the tool on the right to make sure that the formatting is correct.
Otherwise, I think you should contact someone from Celonis to help.

Success!! Thank you Stephanie for persisting with me!
By selecting from the drop-down list it became clear that I needed to include the “.csv” for my event log name as I was using files rather than tables.
Just as an aside, my initial question was wrong, it was actually the Q1, Q2 and Q3 values that I needed, but using your instructions this was easy to do.
Thank you again!

1 Like