Case Count of bundled activities (last occur)

Technical Implementation Codes & Statements
#1

Hey everyone,

I am looking for a solution for the following issue. I have a bundle of 5 different activities. I need to know the count of cases for every activity.

The Problem is that more of one of the bundled activities can occur in one case. Therefore I would like to count only the last occurred activity in the bundle. The required activities can occur somewhere in the process flow.

Example picture attached.

bundle
bundle953×551 57.8 KB

Is there someone who can help me to solve this problem?

1 Like
#2

Hi @SH2020

welcome to the Celonis Community!

Since you can only count each case for one activity, you can first find the last Bundle Activity for each case in the Dimension of your OLAP table:

PU_LAST("Cases", "Activities"."Activity", "Activities"."Activity" IN ( '400-ABC', '400-EBC' ))

As a KPI you can simple use the case count:

COUNT_TABLE("Cases")

You will end up with a row for every bundle activity together with the case count. There will probably be another row with NULL, this is the count of cases where none of those bundle activities is contained. You can either filter this out or replace the NULL value with a string like ‘No Bundle Activity’, depending on what you wish.

Best
David

#3

Hi David,

thank you for the feedback. There seems to be some difficulty in implementing the code in a column chart. The dimension here is the date of the activity:

ROUND_MONTH(PU_LAST(“CASES”,“ACTIVITIES”.“EVENTTIME”,
“ACTIVITIES”.“ACTIVITY” IN (‘400-ABC’, ‘400-EBC’)))

The KPIs should count per CASE the LAST Activity per CASE within the defined activities. However the activities to be counted are not necessarily the last activities in a case. Can you give me another hint regarding the code?

best
Stefan

#4

Hi Stefan,

The formula you posted actually returns the last eventtime per case which is connected to one of the two activities in the filter. So it does not matter if those activities are at the end of the case or not. So the code should do what you expect.
Do you get unexpected results? Do you maybe have other dimensions or KPIs which might influence the result?

Best
David

#5

Hi David,

thanks for your answer. We need a Chart with the following figures:

DIMENSTIONS:
ROUND_MONTH(PU_LAST(“CASES”,“ACTIVITIES”.“EVENTTIME”, “ACTIVITIES”.“ACTIVITY” IN (‘400-ABC’, ‘400-EBC’)))

KPIS:
Pillar stacked per last occurance of specific activity
Pillar (total)

Chart Celonis

We tryed a lot of different variants with PU_LAST/PU_COUNT. Our main issue is that the total number and the stacked number differentiate.

best
Stefan

#6

Hi Stefan,

so although on the screenshots it looks like the numbers match, they actually don’t?
How did you define the KPIs?

Best
David

#7

Hi David,

the graph shows the desired target view (not from Celonis / made in MS PowerPoint). Because of an error message (when using PU LAST function) we have coded it as followed:

<<SINGLE COUNTED KPI 1>>

SUM(CASE WHEN
PU_COUNT(“CASES”, “ACTIVITIES”.“ACTIVITY”,“ACTIVITIES”.“ACTIVITY” =‘400-ABC’ ) > 0 AND
PU_COUNT(“CASES”, “ACTIVITIES”.“ACTIVITY”,“ACTIVITIES”.“ACTIVITY” =‘400-EBC’ ) = 0 AND
THEN 1
ELSE 0
END)

<<SINGLE COUNTED KPI 2>>

SUM(CASE WHEN
PU_COUNT(“CASES”, “ACTIVITIES”.“ACTIVITY”,“ACTIVITIES”.“ACTIVITY” =‘400-EBC’ ) > 0 AND
PU_COUNT(“CASES”, “ACTIVITIES”.“ACTIVITY”,“ACTIVITIES”.“ACTIVITY” =‘400-ABC’ ) = 0 AND
THEN 1
ELSE 0
END)

<>

SUM(CASE WHEN
PU_COUNT(“CASES”, “ACTIVITIES”.“ACTIVITY”,“ACTIVITIES”.“ACTIVITY” =‘400-ABC’
OR “ACTIVITIES”.“ACTIVITY” =‘400-EBC’
) > 0 THEN 1
ELSE 0
END)

But there is a mismatch between the sum of single counted activities and the total counted activities. Du you have an Idea?

best regards

Stefan

#8

Hi Stefan,

I assume that the total count is larger than the sum of the single counted values? This is due to cases which contain both Activities 400-ABC and 400-EBC. Such a case is not counted in the first single counted KPI (because the PU_COUNT of ‘400-EBC’ is not 0), and it is not counted in the second single counted KPI (because the PU_COUNT of ‘400-ABC’ is not 0).
However, this case is counted in the total KPI, because the condition “ACTIVITIES”.“ACTIVITY” =‘400-ABC’ OR “ACTIVITIES”.“ACTIVITY” =‘400-EBC’ evaluates to true.

In order to define the single counted KPIs correctly, you should use the PU_LAST logic again to determine which activity should be counted for the case:
Single Counted KPI 1:

COUNT ( CASE WHEN  PU_LAST("CASES"."ACTIVITIES"."ACTIVITIES",
"ACTIVITIES"."ACTIVITY" IN ('400-ABC', '400-EBC')) = '400-ABC' THEN 1 ELSE NULL END )

Single Counted KPI 2:

COUNT ( CASE WHEN  PU_LAST("CASES"."ACTIVITIES"."ACTIVITIES",
"ACTIVITIES"."ACTIVITY" IN ('400-ABC', '400-EBC')) = '400-EBC' THEN 1 ELSE NULL END )

The overall count should still just be
COUNT("Cases")

Additionally, you should filter out all cases which do not contain any of those two activities:
FILTER PROCESS EQUALS ('400-ABC', '400-EBC');

Best
David