KPI calculation within calculation

mohamedafzal — Fri, 15 Nov 2024 22:08:57 GMT

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?

topic KPI calculation within calculation in Reporting Service & Alerting

KPI calculation within calculation