How to calculate the average number of cases 'touched' per day in a single KPI

#1

Hi,

I am stuck with a pretty simple question: how to calculate the avg. number of cases “touched” per day. If I have an OLAP table with eventdate rounded to “day” I get the number just by using the “case count”, but I am not sure how to aggregate it in a simple KPI.

Thanks!

0 Likes

#2

Hi Maria,

are you using CPM4.5 or the IBC? Then I think you can use PU_COUNT_DISTINCT with the new DOMAIN_TABLE feature:

AVG(PU_COUNT_DISTINCT(DOMAIN_TABLE(ROUND_DAY("ACTIVITIES"."EVENTTIME")), "ACTIVITIES"."_CASE_KEY"))

This will count the distinct cases per day and compute the average from those numbers.

Best,
David

0 Likes

#3

Hi David,

we still have 4.4. But I saw this feature built-in in the default Celonis analysis in 4.4. already(Process Overview):heart_eyes: Just want to use the KPI in a different color and in another spreadsheet.

Best regards,
Maria

1 Like

#4

Hi Maria,

the Number in the Process Overview is calculated like this:

COUNT ( DISTINCT "ACTIVITIES"."_CASE_KEY" ) / COUNT ( DISTINCT ROUND_DAY ( "ACTIVITIES"."EVENTTIME" ) )

Hope this helps!

David

1 Like

#5

Hello David!

Hm…but that’s actually not the KPI I thought it to be:) It can be the case that one CASE is processed on multiple days and I would want to keep it counted for every day when some activity for this CASE took place. I think i’ll just wait until 4.5. But thanks a lot for a sneak peek of the built-in KPIs of Celonis:)

Sincerely,
Maria

0 Likes