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