Filter Table based on aggregation

Hi everyone,

Currently I have a table where I list all my process activities, where one of the KPI’s is a calculation of the ratio of cases where a given activity is present.

The calculation I’m making to get this is the following:

COUNT(“CASES”.“CASE_ID”)/GLOBAL(COUNT(“CASES”.“CASE_ID”))

I would then like to filter this table, so that it only shows me the activities that have a ratio of less than 1%. However, Celonis does not allow to filter on aggregate functions.

I’ve tried other workarounds, unsuccessfully.

Is there a way for me to accomplish this and show only the desired activities?

Thanks in advance for your help,
João

Hi João,

The following approach should resolve your issue:

  1. Create a variable of type static_value: ‘cnt_cases’ = GLOBAL(COUNT_TABLE(CASES))
  2. Create your OLAP with the Activity as the dimension and the calculation of the ratio as you described above (you could then also use the <%= cnt_cases %> variable for calculating the ratio).
  3. Add the following component filter:
    FILTER PU_COUNT_DISTINCT(DOMAIN_TABLE(“ACTIVITY_TABLE”.“ACTIVITY”), “ACTIVITY_TABLE”.“CASE_ID”)/(<%= cnt_cases %>) < 0.01;

This will then show you all the activities with a case ratio of less than 1%.