Skip to main content
Question

Case Count of bundled activities (last occur)

  • January 21, 2021
  • 8 replies
  • 11 views
S
Level 3

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.

bundle953551 57.8 KB

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

8 replies

D
Level 1
Forum|alt.badge.img+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
S
Level 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

D
Level 1
Forum|alt.badge.img+2

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

S
Level 3

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)

https://aws1.discourse-cdn.com/business6/uploads/celonis4/original/2X/1/1df71c7e2e7636af8f4f3d0d595f2558071ddd89.jpegWe 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

D
Level 1
Forum|alt.badge.img+2
Hi Stefan, so although on the screenshots it looks like the numbers match, they actually dont? How did you define the KPIs? Best David
S
Level 3

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

D
Level 1
Forum|alt.badge.img+2
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
S
Level 3
Hi David, many thanks. So we now have exactly the evaluation we need. Best Stefan
Community Code of ConductAccessibility statement