Filtering KPIs in OLAP tables with variables

#1

Hello,

is there a way to filter KPIs in OLAP tables? We have an OLAP table with several KPIs like rework rate, price deviation rate etc. We would like to enable the user to filter this OLAP table by means of percentage thresholds per KPI. What is the best way to do that? Using the different KPIs in the component filter does not work.

Best regards
Thomas Nagel

0 Likes

#3

Hi Thomas,

just to check that I understood your question right: you want to provide a filter where you can filter on for example values where rework rate is below 25% and price deviation rate is above 50%?

Best regards,
Viana

0 Likes

#4

Hello Viana,

exactly. I would like to be able to filter the KPIs in OLAP tables in order to identify for example the “critical” vendors which have a bad performance KPI.

Best regards
Thomas

0 Likes

#5

Hi Thomas,
It is not possible to filter on KPIs which are aggregated within the OLAP table however here is a possible work around.
First, you have to create specific buttons, where you can select the % threshold for the KPIs (i.e Button dropdown with a variable "KPI1_critical_limit " and Manual Input with thresholds of your choice i.e. >10%, >15%, >20%…, >80% - IMPORTANT, the variable for this button needs to be 0.1, 0.15, 0.2 …, 0.8 --> Celonis needs the “.” as separator for decimal values). If you want to add an upper and a lower threshold then you need two buttons per variable.
Second, add a new KPI to your table with a name like ‘Critical’. Here you can use a case when statement to find the cases where all the KPIs are within the thresholds specified in the drop down.

CASE WHEN KPI(YOUR_SELECTED_KPI_1) >= <%= KPI1_critical_limit %> AND KPI(YOUR_SELECTED_KPI_2) <= <%= KPI2_critical_limit %> AND … [add all KPIs filtered on]
THEN ‘Yes’ ELSE ‘No’
END

After that you need to sort the entire OLAP table according the new variable ‘Critical’ so the ‘Yes’ are on top. I would also advise adding colour mapping to make the critical values really stand out.
Hope this helps.

Best regards,
Viana

1 Like

#6

Hi nagalt/Viana,

another possible solutions is to use pull up functions.

From celonis help (https://help.celonis.cloud/display/CIBC/Pull+Up+Aggregation+Functions)

The Pull-Up-Functions allow you to aggregate a column based on another table. You can define the parent table to which the child-tables entries are pulled, and you can explicitly define on which basis calculations are executed. The way PU functions handle filters is different compared to the standard aggregation. In contrast to the standard aggregation, PU functions ignore filters, meaning that if a filter or a selection is changed, the result of the PU function is not recalculated. Another difference to the standard aggregation is that it is possible to filter on the result of a PU function.

It is not possible to use the same table as the parent and the child table in an PU function. However, the DOMAIN_TABLE function can be used to create a temporary table from the parent tables’ column(s) to pull up to.

Maybe this is helpfull as well!

Best,
Jasper

2 Likes