Functions/KPIs without Accounting for Filter

Hi,

New to Celonis here.

Is there way to write certain KPIs that do not get affected by selections & filters?
Basically, I guess static variables that get sliced by dimensions?

For example, on a OLAP table, if I was looking at Return Goods along customer dimensions and have Total Orders % (# of orders returned/ Total Order Count not respecting the return goods filter.). And if I change to a different dimension, I would want the KPI to recalculate too.

Basically, if I filter to just returned goods, is there way to keep the total order count in the above formula refer to overall total count while being sliced by dimensions.

Also, is there a recommended way to analyze such comparisons in general?

Hi,

you might want to take a look at PU aggregations. PU functions don’t respect the filters. For example, the following query would count the number of orders per customer, while not respecting the filters on orders:

PU_COUNT(“Customers”, “Orders”.“OrderID”)

Depending on the dimension you are using, you might need DOMAIN_TABLE in the first argument of the PU function. Please check the Documentation on PU functions for examples.

Let me know if you have any more questions.

Best
David

2 Likes

Hi,

see also our community post on pull functions for an introductory read (not complete): The longest but ultimate guide for being a Pull-Up-Functions expert

Best
Manuel from Celonis

1 Like

Thanks for the replies. Learning about PU Functions.
Might need some help still.

Using the default demo O2C analysis Celonis provides in its Snap environment, if I use the following formula:
PU_COUNT(“KNA1”,“VBAP”.“POSNR”)
the OLAP table splits the customer into multiple line entries. For example, “Queen Industries 11044” has 10+ lines now.

Also, I am prevented from performing mathematical operations on this PU function. Example: COUNT_TABLE(“VBAP”)/PU_COUNT(“KNA1”,“VBAP”.“POSNR”)

Let me know where I am having a gap in my understanding. Thanks!

Regarding your first question:
There are multiple customers in KNA1 with the same name. You can add the “KNA1”.“KUNNR” column to your OLAP table, which contains the customer number. This should be different for all the “Queen Industries 11044” entries.
The PU function as I have proposed groups by the primary key of the KNA1, which is based on the KUNNR and not on the Name.

Second question: the result of a PU function behaves like a regular column of the table you specify in the first argument. You cannot combine aggregations and normal columns in the same dimension. This is like in SQL, there you also cannot do a COUNT(column)/column.
Easiest way to fix this in your situation would be to use a static value variable containing the COUNT_TABLE(“VBAP”) formula.

Best
David

1 Like

Thanks for answering my questions @d.becher