Skip to main content

Reporting Service

This forum is for discussions around reporting from Qlik Cloud.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mohamedafzal
Contributor III
Contributor III

KPI calculation within calculation

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?

0 Replies