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)
+2
"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
+2Hi 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, its 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
+2Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.