Count Cases when Value in Column "Repair in time" = 1

Hello

Please send help! I’m desperatly trying to count all cases with “repair in time” = 1 (true)
If not repaired in time there’s 0. SUM doesn’t work, because there is more than one activity per case, so there are many rows matching the same case id in the data base.
I’m looking for the percentage of cases repaired in time (of total cases) carried out by one service point.

My KPI:
AVG( CASE WHEN "EventLog_UPLOAD_csv"."REPAIR_IN_TIME" = 1 THEN 1.0 ELSE 0.0 END)

Unfurtonately, it counts rows, not cases. So the result isn’t exact.
Can someone help?

Hi @f.eleonora

Your query is based on the Activity table, which is why the AVG is based on the number of rows in the Activity table. You can use e.g. CALC_REWORK, which returns the number of rows with a specified condition for each case:

AVG ( CASE WHEN CALC_REWORK( "EventLog_UPLOAD_csv"."REPAIR_IN_TIME" = 1 ) > 0 THEN 1 ELSE 0 END )

This returns the % of cases where at least one activity was repaired in time.

Best
David