Throughput time until a specific activitiy from cases with certain (other) activities in OLAP

Hi Community,
I try to create an OLAP Table, that looks like this:

The aim is to count cases and calculate lead times. Starting from creation of the case until a specific activity occurs. But only if another specific activity is found in the case.
Then count the number and calculate the average lead and display it as a columns in the OLAP-table.
The products ought to be in the lines of the OLAP.
grafik

Thanks for your help.
Stefan

Hi @StefanZiegelboeck ,

Insert the product name as OLAP-Dimension, e.g.
“PRODUCT_TABLE”.“PRODUCTNAME”.

First OLAP-KPI:
We only want to count the cases with activity ‘A’ and without activity ‘C’:

SUM( CASE
WHEN ( ISNULL(PU_FIRST(“CASE”.“TABLE” , “ACTIVITY_TABLE”.“ACTIVITY” , “ACTIVITY_TABLE”.“ACTIVITY” = ‘A’))=0 AND (PU_FIRST(“CASE”.“TABLE” , “ACTIVITY_TABLE”.“ACTIVITY” , “ACTIVITY_TABLE”.“ACTIVITY” = ‘C’))=1 )
THEN 1.0
ELSE 0.0 END)

Second OLAP-KPI:
We only want to calculate the average lead time of the cases selected with our first KPI:

AVG(CASE
WHEN ( ISNULL(PU_FIRST(“CASE”.“TABLE” , “ACTIVITY_TABLE”.“ACTIVITY” , “ACTIVITY_TABLE”.“ACTIVITY” = ‘A’))=0 AND (PU_FIRST(“CASE”.“TABLE” , “ACTIVITY_TABLE”.“ACTIVITY” , “ACTIVITY_TABLE”.“ACTIVITY” = ‘C’))=1 )
THEN CALC_THROUGHPUT( CASE_START TO FIRST_OCCURENCE [‘D’] , REMAP_TIMESTAMPS(“ACTIVITY_TABLE”.“ACTIVITYSTART”, DAYS) )
ELSE NULL END)

Can you please specify on the second part of your table, why the number of cases differs from the first part of the table?
In both cases we do count Cases with Activity ‘A’ and without Activity ‘C’ therefore it should display the same number.

For calculating the throughput time from START to Activity ‘E’ just change ‘D’ to ‘E’ in the above example i.e. :

AVG(CASE
WHEN ( ISNULL(PU_FIRST(“CASE”.“TABLE” , “ACTIVITY_TABLE”.“ACTIVITY” , “ACTIVITY_TABLE”.“ACTIVITY” = ‘A’))=0 AND (PU_FIRST(“CASE”.“TABLE” , “ACTIVITY_TABLE”.“ACTIVITY” , “ACTIVITY_TABLE”.“ACTIVITY” = ‘C’))=1 )
THEN CALC_THROUGHPUT( CASE_START TO FIRST_OCCURENCE [‘E’] , REMAP_TIMESTAMPS(“ACTIVITY_TABLE”.“ACTIVITYSTART”, DAYS) )
ELSE NULL END)

Hope this helps!

Hi, Thanks for your help!
It seems to work now but i made a change to it. In the KPI is one ISNULL function:

To work it seems to need a second ISNULL function right after the AND, so it looks like this:

SUM( CASE
WHEN ( ISNULL(PU_FIRST(“CASE”.“TABLE” , “ACTIVITY_TABLE”.“ACTIVITY” , “ACTIVITY_TABLE”.“ACTIVITY” = ‘A’))=0 AND (ISNULL (PU_FIRST(“CASE”.“TABLE” , “ACTIVITY_TABLE”.“ACTIVITY” , “ACTIVITY_TABLE”.“ACTIVITY” = ‘C’))=1 )
THEN 1.0
ELSE 0.0 END)

Was it intended that way?

As to your question:

I made a mistake in the table above, it should be:

Difference is that the second case count should include cases with activity A and with activity C.

I gues the adapted version of would look like this:

SUM( CASE
WHEN ( ISNULL(PU_FIRST(“CASE”.“TABLE” , “ACTIVITY_TABLE”.“ACTIVITY” , “ACTIVITY_TABLE”.“ACTIVITY” = ‘A’))=0 AND (ISNULL(PU_FIRST(“CASE”.“TABLE” , “ACTIVITY_TABLE”.“ACTIVITY” , “ACTIVITY_TABLE”.“ACTIVITY” = ‘C’))=0 )
THEN 1.0
ELSE 0.0 END)

right?

Hi @StefanZiegelboeck,

It was intended as you described it.
You are also right with your assumption on the adapted version.

Great to see that you solved the problem yourself!

Hopefully, everything works now!

Happy Mining!

Justin

1 Like

One of our business user has a question i am struggeling to answer. We uses the formular to calculate the median throughput time of some cases containing specific activities. The result is 38 minutes.
In another KPI we calculate the percentage of cases below a certain lead time. We expect to see roughly 50% of cases if we set the lead time to the 38 minutes we got from the other KPI. The result in app. 32%. Here is the formula i used:

AVG(CASE WHEN
ISNULL(PU_FIRST(“CASE”, “ACTIVITY_TABLE”.“ACTIVITY”, “ACTIVITY_TABLE”.“ACTIVITY” = ‘A’)) >= 1 AND
ISNULL(PU_FIRST(“CASE”, “ACTIVITY_TABLE”.“ACTIVITY”, “ACTIVITY_TABLE”.“ACTIVITY” = ‘B’)) 1>= 1 AND
CALC_THROUGHPUT(CASE_START TO LAST_OCCURRENCE[‘C’], REMAP_TIMESTAMPS(“ACTIVITY_TABLE”.“ACTIVITYSTART”, MINUTES)) <= 38
THEN 1.0 ELSE 0.0 END)

So how does one correctely interpret the results? isn’t it valid to expect app. 50%?

Dear Stefan,

thanks for reaching out to the Celonis Community Forum.
In your formula you used the AVG() function, therefore it is actually not valid to expect approximately 50% as the average is very prone to outliers:
In a set of [1, 2, 3, 4, 5, 6, 7, 8, 9, 100] the average would be 14.5, and 90% of values would be lower.
Try to use the MEDIAN() function in your formula. This should help.

Best regards,
your Celonis Community Forum Team

Thanks for the fast reply.

I put in the median function instead of AVG with the result of 0,00%.

This is what I would expect as the formula is nested in a way that all cases are rated with 0 for fulfilling the clauses of the case when statement, or with 0 when not fulfilling them. As most cases don’t fulfill them the median is 0, that makes sense.

Leaves the original question still open, might there be another explanation?