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

---

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

---

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

---

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)

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

---

Hi Stefan,
so although on the screenshots it looks like the numbers match, they actually dont?
How did you define the KPIs?
Best
David

---

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

---

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

---

Hi David,
many thanks. So we now have exactly the evaluation we need.
Best
Stefan

---