CASE WHEN with aggregation

Hey all,

I have the following OLAP Table with dimensions from “Sales Org Nr” on the very left until “Activity”. The #Touches KPI Column should show values based on the “Activities_table”.“USER_TYPE” field.

If it is ‘A’ it should show the result of a function we developed:
ROUND( SUM( “_CEL_O2C_ACTIVITIES”.“ACTIVITY_COUNT”) + 0.49)

If it is not ‘A’ it should just show 0.

I tried
CASE WHEN “_CEL_O2C_ACTIVITIES”.“USER_TYPE” = ‘A’ THEN ROUND( SUM( “_CEL_O2C_ACTIVITIES”.“ACTIVITY_COUNT”) + 0.49) ELSE
0.00 END
but it brings the error:

Anyone has an idea what I need to change. I assume it is dimension-related.

Thanks for a hint.

Best,
Marcel

Hi Mankem,

The problem doesn’t lie in your dimension but rather in your case when statement. You are using an aggregation (SUM) in a case when statement and that’s throwing an error because Celonis doesn’t know how to aggregate when its inside a Case when loop as Case When statement is going through one of the tables line by line checking if the condition is fulfilled (in this case the activity table to check the user type). The way you work round this is to use a pull function that explicitly states that the aggregation takes place on the level of the dimensions of the table. If you write your KPI like this, it should work:

CASE WHEN “_CEL_O2C_ACTIVITIES”.“USER_TYPE” = ‘A’ THEN

ROUND(PU_SUM(DOMAIN_TABLE(“VBAK”.“VKORG”,, “_CEL_O2C_ACTIVITIES”.“USER_TYPE”,"_CEL_O2C_ACTIVITIES"),
“_CEL_O2C_ACTIVITIES”.“ACTIVITY_COUNT”)) + 0.49

ELSE 0.0

END

If this doesn’t solve your problem please write back with more information about your “_CEL_O2C_ACTIVITIES”.“ACTIVITY_COUNT” column (the data set I tested the solution on doesn’t have this column).

Best wishes,

Calandra