Strange Results in Ratio KPI

Hello,

i’ve the following issue:
The PQL Formula “Ratio of Cases flowing through a dedicated activity” is used to determine the % of cases that do contain activity “Remove Delivery Block”. Putting this formula into a number element - works fine. Shows 20%.

The same 20% appears when the activity “Remove Delivery Block” is marked in the process explorer.

In my example i have 340 cases, where 68 do have “Remove Delivery Block”.

The 340 cases to have 4389 activities in total, the 68 cases having the acitvity “Remove Delivery Block” do have 1229 activities.

If the KPI Ratio is put into an OLAP Table and shown with dimensions like Company or Sales Org the Ratio moves from 20% to 28%. I figured out that the formula is changing from a case % (68 out of 340) to an activity related calculation (1229 out of 4389) - this does always happen if any characteristic that is joined to the activity table is shown in the OLAP Table.

In addition - the same applies - the other way around if sum(vbap.net_order_value) is taking place and a activity based condition is applied - the order value is getting multiplied with the number of activities.

Is there any solution that i can apply? Thanks in advance.

Regards,

Holger

Hi Holger,

welcome to the Celonis Community!

What you are searching for is most probably the PU Function. It helps you to aggregate correctly when working on different levels like VBAP and the Activty Table. In the PU Function you can also define conditions (see PQL refernce in the code editor).
For working with the PU Functions, please have a look in the Celonis Help Material and additionally you can find a very good guide here:

Hope this helps!

Best regards,
Celonis Data Science Team

Hi,

thanks for the reply. I’m aware of the pull-up function - i was just wondering that a build-in KPI formula does not work properly. I take your answer as recommendation to not use the build-in formula and replace it by custom made one.

Keep you updated.

Regards,

Holger

Hi Holger,

thank you for reaching out.

Actually the built-in KPI works as intended, as we can visualize both the ratio based on cases and based on activities.
Depending on the use cases, boths view might be valuable, as the case-based ratio shows you the number of unique IDs flowing a certain path and the activity-based ratio can show you detailed information about an unwanted activity occurring in XX% of activities.

Please see below an example on how both ratios can be displayed. Choose the option that fits your needs best.

image

In case of any question, please feel free to reach out.

Best regards,

David Buchmann

Dear David,

works as designed :slight_smile:

The correct solution (to be consistent to the process explorer) is provided by the following formula:

COUNT (DISTINCT CASE WHEN “_CEL_O2C_ACTIVITIES”.“ACTIVITY_EN”=’<%= check_act %>’ THEN “_CEL_O2C_ACTIVITIES”."_CASE_KEY" ELSE NULL END)

where the Variable <%= check_act %> contains the desired activity. The result will be the distinct number of cases - dividing them by COUNT_TABLE(“CASETABLE”) will lead to the equal result as KPI like the process explorer.

Hi imhme,

did you sort out the order value get multiplied by number of activities issue?

Thanks,
Denny