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

)

Because I am using an OLAP table, I cannot specify the specific activity, as it is already the dimension for the OLAP table. That is why the following does not work;

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

If there is another solution that is not in an OLAP table I am of course also interested