Skip to main content

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

Hi Jan,

to show the Activities and count them do the following

  • Create a table with dimension "ACTIVITY_TABLE"."ACTIVITY"
  • Create a KPI with COUNT("ACTIVITY_TABLE"."ACTIVITY")

Then it should work. In that table you also see which Activities occured >=2 times.

Alternative is a formula for that which can be used e.g. for filtering

FILTER PU_COUNT(DOMAIN_TABLE("ACTIVITY_TABLE"."ACTIVITY"), "ACTIVITY_TABLE"."ACTIVITY") >=2;

 

br

Daniel

Reply


Terms & ConditionsCookie settings