Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
thomasmercer
Contributor III
Contributor III

Calculating the average age of open incidents over time

Hi,

I already store the dates in the source table and then in a "master calendar" table to make things simpler in set analysis depending on what I'm calculating.

The data set I have is something like the following (DD/MM/YYYY format):

incident_numberdate_incident_reporteddate_closed
101/02/201906/05/2019
203/02/2019 
310/02/201903/03/2019
414/02/201901/08/2019

 

incident_numberDateTypeSharedDate
1date_incident_reported01/02/2019
1date_closed06/05/2019
2date_incident_reported03/02/2019
3date_incident_reported10/02/2019
3date_closed03/03/2019
4date_incident_reported14/02/2019
4date_closed01/08/2019

 

Tables are linked on incident_number

The following formula works with the master calendar to output the number of incidents open at any date by calculated the total number of incidents which were reported minus the number that had been closed:

 

 

(RangeSum(above(Total Count({<[ScoutYear]=,[ScoutQuarter]=,[ScoutMonth]=,[SharedDate]=,DateType={'date_incident_reported'}>} distinct [incident_number]),0,RowNo())) * Avg(1))
-
(RangeSum(above(Total Count({<[ScoutYear]=,[ScoutQuarter]=,[ScoutMonth]=,[SharedDate]=,DateType={'date_closed'}>} distinct [incident_number]),0,RowNo())) * Avg(1))

Where I'm stuck is trying to similarly calculate the average age of incidents which are open at any point in time, whcih I'd like to calculate based on the master calendar SharedDate value.

The business logic is:

1. Check if the incident was open at SharedDate (SharedDate > date_incident_reported and SharedDate < date_closed)

2. For above incidents calculate the average number of days between date_incident_reported and SharedDate

 

Any ideas?

Thanks,

Thomas

 

1 Reply
thomasmercer
Contributor III
Contributor III
Author

Can anyone help? I think there might be a way to do this with intervalmatch but I haven't had any luck with this.