How to count unique cases with multiple occurrences of an activity?

In my output (OLAP) table I want to have each activity name from the activity table and then an overview of the number of cases in which that activity name has occurred. Then I want to get an overview of the number of cases in which that activity name has occurred two times, three times, or multiple times etc.

I know that with the following code I can get the number of cases where the activity happened at least 1 time, but I do not know how to calculate the number of cases where the activity happened 1 time and not more often.

SUM(

CASE WHEN

INDEX_ACTIVITY_TYPE ("ACTIVITY_TABLE"."ACTIVITY") = 1

THEN 1

ELSE 0

END

)

The other thing I still miss is how I can calculate the number of cases in which that activity occurred >=2 times. When I change the formula to >=2, I get the wrong count as then a case with 4 activity occurrences is counted as 3 instead of 1 (it counts the second occurrence, third occurrence and fourth occurrence all as one 'case'). Is there anyway to count only the distinct cases?

Could we instead of SUM '1', do something with counting the distinct case id for example? I get errors when trying to do something like that.

SUM(

CASE WHEN

INDEX_ACTIVITY_TYPE ("ACTIVITY_TABLE"."ACTIVITY") = 1

THEN COUNT (DISTINCT "case_table"."case_key")

ELSE 0

END

)

Page 1 / 1

Hi @jan.van.d12,

For this use case you can use a combination of SUM, CASE WHEN, and CALC_REWORK

It's been a while for me, so pardon if this is not correct in the first go.

It would be something like this

SUM(CASE WHEN CALC_REWORK("Activities"."ACTIVITY" IN ('B')) = THEN 1 ELSE 0 END)

What this code essentially does is validate whether CALC_REWORK returns X times. So for instance, if you only want cases where an activity returned more than once, it would like this.

SUM(CASE WHEN CALC_REWORK("Activities"."ACTIVITY" IN ('B')) > 1) THEN 1 ELSE 0 END)

Let me know if this puts you in the right track!

have you tried PU_Count_Distinct ?

Hi @jan.van.d12,

For this use case you can use a combination of SUM, CASE WHEN, and CALC_REWORK

It's been a while for me, so pardon if this is not correct in the first go.

It would be something like this

SUM(CASE WHEN CALC_REWORK("Activities"."ACTIVITY" IN ('B')) = THEN 1 ELSE 0 END)

What this code essentially does is validate whether CALC_REWORK returns X times. So for instance, if you only want cases where an activity returned more than once, it would like this.

SUM(CASE WHEN CALC_REWORK("Activities"."ACTIVITY" IN ('B')) > 1) THEN 1 ELSE 0 END)

Let me know if this puts you in the right track!

Hi @sverre.klein11 ,

Thank you for your answer! I do understand your logic, but I am still figuring out how I can apply this logic in an OLAP table. The "ACTIVITY_TABLE"."ACTIVITY" is already the dimension of the OLAP table, and then I want to make a KPI that calculates for each activity (in the dimension), how many cases appear where that activity occurred multiple times. When I try to work with your formulas, it does not work as I do not have the activity 'B'. So I need to be independent of a specific activity name.

Would you know how to resolve that issue?

Thanks in advance!

have you tried PU_Count_Distinct ?

I have not got that to work, how would you apply this in my use case?

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN