Aggr Expression

[Jacklin24]

I want calculate process success rate but the data is repetitive and I cannot make it unique as there are other column constraints. Please help form qlik expression for kpi chart to calculate success rate of processes. 
For thse data below, process 1 succed once and process 2 succeed twice, so success rate as a whole is 60%. 
Please help form correct expression!

Process ID	Process startTimestamp	process end	process status
1	9 Jan 2024 10:00 AM	9 Jan 2024 10:30 AM	Success
1	9 Jan 2024 10:00 AM	9 Jan 2024 10:30 AM	Success
1	9 Jan 2024 10:30 AM	9 Jan 2024 11:00 AM	Failed
2	9 Jan 2024 10:00 AM	9 Jan 2024 10:30 AM	Failed
2	9 Jan 2024 10:00 AM	9 Jan 2024 10:30 AM	Failed
2	9 Jan 2024 10:30 AM	9 Jan 2024 11:00 AM	Success
2	10 Jan 10:00 AM	10 Jan 11:00 AM	success

[marksouzacosta]

Hi @Jacklin24 ,

I would recommend some changes on the Data Model but if that is not possible, try this:

Sum(Aggr(Count({<[process status] = {'Success'}>} DISTINCT [Process startTimestamp]), [Process ID]))
/
(
Sum(Aggr(Count({<[process status] = {'Success'}>} DISTINCT [Process startTimestamp]), [Process ID]))
+
Sum(Aggr(Count({<[process status] = {'Failed'}>} DISTINCT [Process startTimestamp]), [Process ID]))
)