I have the data as attached with Transction Id, Event and Effective Date.
I need to calculate a KPI - Number of Transaction which couldn't be registered within 30 mins of claims submission, which is - need to calculate the Turn Around Time(TAT) between Register Date(Event=500) and Submission Date(Event=300) and get the Distinct count of Transaction Id.
| Trans_Id |
EventCode |
Event_Name |
Eff_DateTime |
| 1 |
100 |
Transaction_ Start |
05/12/2022 13:55 |
| 1 |
300 |
Transaction_Submit |
05/12/2022 13:55 |
| 1 |
500 |
Transaction_Register |
05/12/2022 14:26 |
| 2 |
100 |
Transaction_ Start |
05/13/2022 13:55 |
| 2 |
300 |
Transaction_Submit |
05/13/2022 13:55 |
| 2 |
500 |
Transaction_Register |
05/13/2022 13:59 |
| 3 |
100 |
Transaction_ Start |
05/14/2022 13:59 |
| 3 |
300 |
Transaction_Submit |
05/14/2022 13:00 |
| 3 |
500 |
Transaction_Register |
05/14/2022 13:29 |
| KPI calculation |
Count Distinct Transaction_Id where (EventTime 500 -EvetntTime 300)>=30 |
From the above date, TAT for 1=31 min, TAT for Trans_Id 2= 4 min, TAT for Trans_id 3 = 29 min.
The KPI value should be 1.
To achieve the above in KPI we tried the below KPI expression:
count({<"=interval(only({<EventCode={'500'}>} Eff_DateTime)-only({<EventCode={'300'}>} Eff_DateTime),'mm')"={">30"}>} distinct Trans_Id).
But the set analysis expression are not considered and we get the Total Distinct count of 3 as the KPI Value.
Could someone please help on the above?
