Total number of cases in OLAP


#1

Hello,

I like to show in a column in an OLAP table the percentage of a particular activity compared to the total number of cases.
Dimension is Activity
KPI: COUNT("_CEL_P2P_ACTIVITIES".“ACTIVITY_EN”)/COUNT_TABLE(“EKPO”)

is always 1 as the COUNT_TABLE is alwasy adapted to the current line - how can I adress the number of total cases in an OLAP table?
best regards
Christian


#3

Hello Christian - I think you need to multiply your COUNT(_CEL_P2P_ACTIVITIES.ACTIVITY_EN) by 1.0 to to change the data type from an integer to a float. So the formula:
(COUNT(_CEL_P2P_ACTIVITIES.ACTIVITY_EN) * 1.0) / COUNT_TABLE(EKPO) should return the ratio in a float data type format.

-Tyler


#4

Hello Tyler,
thank you but forcing to double doesn’t solve the issue.
the Count_Table is in each OLAP row dependent on the dimension (activity) but I like to compare to all cases in total - I put the two parts of the formular in two columns and the count_table should be always the number of total selected cases (~ 4 mio)
e.g.
grafik

best regards
Christian


#5

Ah sorry I misunderstood your question. The only way that I could think of doing this would be to create a KPI for each activity, rather than using ACTIVITY_EN as the dimension. You could create a KPI like this for each activity that you want to measure:

SUM(CASE WHEN _CEL_P2P_ACTIVITIES.ACTIVITY_EN = ‘Goods Receipt’ THEN 1.0 ELSE 0.0 END) / COUNT_TABLE(EKPO)

With this method, you would have to create a KPI for each activity for which you want this measure (swapping out ‘Goods Receipt’ for each activity you’re measuring). Perhaps the gurus at Celonis have a better way to do this :sweat_smile:


#6

Hello Christian,

Not sure if this is what you want but it will display, for each activity, the percentage of total activities each one represents.

  1. Create a ‘static value’ variable in Analysis settings for the total # of activities (so the denominator stays the same) (e.g Variable_1). (I believe static variables are only available in Celonis 4.4):

  1. Create the ‘% of total activities’ KPI with that variable:

Formula: 1.0*COUNT_TABLE("_CEL_AP_ACTIVITIES")/<%= Variable_1 %>

  1. ‘Activity name’ as dimension, ‘Activity count’ and ‘% of total activities’ as KPIs


#7

Another option is to check the box “Calculate share in percentages” and specify COUNT_TABLE as “Total 100%” field. In this case COUNT_TABLE will not adjust to specific row in table.

image