Sum of Distinct count of activities in case statement

Hi Community,
I need some help on this logic in code editor to give % of completed orders at second time.

Sum(All “Completed” orders with conditional activity = 1 Reason) & Sum of all completed orders
There are multiple “REASONS” in Activity column but i need only completed orders with one “REASON”

Trying to calculate the % from a table based on certain activities but limited to the count = 1 .

Can you please help with the case statement how to get the % based on above. Here i want to get only count of REASON = 1 for the status & activity = completed for each case.

COUNT (DISTINCT CASE
WHEN
“table”.“ACTIVITY” = ‘REASON’
AND
“table”.“status” = ‘COMPLETED’ AND
“table”.“ACTIVITY” like ‘%COMPLETED%’
THEN ‘1 REASON’
WHEN
“table”.“ACTIVITY” != ‘REASON’
AND
“table”.“status” = ‘COMPLETED’ AND
“table”.“ACTIVITY” like ‘%COMPLETED%’
THEN ‘NO REASON’
ELSE ‘OTHERS’
END
)

Thanks

Hello bavajips,

I am not sure if I fully understand what you want to implement. Could you please provide a screenshot of the activity columns which show the different ‘REASONS’. Because I don’t understand what you exactly mean with orders with one “REASON”. Also, what is the content of the variable %COMPLETED%’ ?
But here is a solution proposal from what I understood:
SUM(
CASE
WHEN PU_COUNT(DOMAIN_TABLE(“table”."_CASE_KEY")
, “table”.“ACTIVITY”, “table”.“ACTIVITY” = ‘REASON’
AND “table”.“status” = ‘COMPLETED’
AND “table”.“ACTIVITY” like ‘%COMPLETED%’) = 1 THEN 1.0
ELSE 0.0
END)
/
SUM(
CASE
WHEN PU_COUNT(DOMAIN_TABLE(“table”."_CASE_KEY")
, “table”.“status” = ‘COMPLETED’
AND “table”.“ACTIVITY” like ‘%COMPLETED%’) >= 1 THEN 1.0
ELSE 0.0
END)

The first sum in the function sums up all the cases which only have one reason and have been completed. The second sum of the function sums up all cases which have been completed. Therefore, the percentage of completed cases with one reason is calculated by dividing the first sum through the second sum.

Best regards,
Your Data Science Team