Skip to main content
Question

Filter Table based on aggregation

  • May 8, 2020
  • 1 reply
  • 32 views
J
Level 1
Hi everyone, Currently I have a table where I list all my process activities, where one of the KPIs is a calculation of the ratio of cases where a given activity is present. The calculation Im 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. Ive 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, Joo

1 reply

C
Level 0
Hi Joo, 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%.
Community Code of ConductAccessibility statement