Hi Jan,
I hope I understood your question correctly.
Hope this helps:
OLAP table
ACTIVITY_COLUMN:
"ACTIVITY_TABLE"."ACTIVITY"
COUNT_1:
SUM(
CASE WHEN
INDEX_ACTIVITY_TYPE ("ACTIVITY_TABLE"."ACTIVITY") = 1
THEN 1
ELSE 0
END
)
COUNT_2:
SUM(
CASE WHEN
INDEX_ACTIVITY_TYPE ("ACTIVITY_TABLE"."ACTIVITY") = 2
THEN 1
ELSE 0
END
)
COUNT_3:
SUM(
CASE WHEN
INDEX_ACTIVITY_TYPE ("ACTIVITY_TABLE"."ACTIVITY") = 3
THEN 1
ELSE 0
END
)
Result:
This should show an OLAP table with 1 row per activity and the number of cases where the number of times is the same as stated in the formula (1, 2, 3).
Great Mathijs, thank you!
That was indeed what I wanted to know! The only 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?
Thanks again!
And also when calculating the lead time for cases with exactly 1 occurrence of the activity I cannot use INDEX_ACTIVITY_TYPE ("ACTIVITY_TABLE"."ACTIVITY") = 1 because then it will also consider cases with multiple occurrences. How can I then select ONLY cases with EXACTLY one occurrence of that activity versus distinct cases with more than one occurrence of that activity?
