Dear All i would like to build a KPI based on an OR condition But somehow the result is more than 100% how can i combine that per case? thank you
SUM(CASE WHEN “EKPO”.“LABNR” = ‘1’ OR “CEL_P2P_ACTIVITIES”.“ACTIVITY_EN” = ‘Create Order Confirmation’ THEN 1.0 ELSE 0.0 END)
/
COUNT_TABLE(“EKPO”)
Hi Marco,
Since you introduce the join to the Activity table, the sum will be based on the Activity table accordingly. So whenever "EKPO"."LABNR"=1, the number of activities for this case will contribute to the sum.
I assume you want to apply the KPI on case level, e.g. count the ratio of cases where "EKPO"."LABNR"=1 or which contain the activity 'Create Order Confirmation' at least once.
In this case, I would suggest to not introduce the Join with the Activity table, but use PROCESS EQUALS instead to keep it on Case table level. You can then also use AVG to calculate your KPI:
AVG ( CASE WHEN "EKPO"."LABNR"=1 OR PROCESS EQUALS 'Create Order Confirmation' THEN 1 ELSE 0 END )
Best
David
Hello David, thank a lot that works great. Just another issue that comes up if I want to add another KPI to the same component, it calculated weired values. Seperately it works fine. 
Hi Marco,
This is due to the same issue as you had before. If you add another KPI which is only based on the Case table, it’s fine. If you add another KPI which is based on the Activitiy table, all calculations in that component will be based on the Activity table again. So you need to re-write the other KPI such that it is based on the Case table. It depends on your KPI how to do this, in most cases you can use PU functions to first aggregate your Activity table to Case level, and a regular aggregation around that to aggregate per Material Group.
Best
David
Hello David, thanks. Is there any suitable entries about PULL Functions and Cases When statements? I have checked but haven´t found anything
Hi @MarcoM
what do you mean with “entries”? You can check out the Documentation on help.celonis.cloud about how to use PU functions and CASE WHEN statements.
Best
David