# 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
Did you mean:
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_number date_incident_reported date_closed 1 01/02/2019 06/05/2019 2 03/02/2019 3 10/02/2019 03/03/2019 4 14/02/2019 01/08/2019

 incident_number DateType SharedDate 1 date_incident_reported 01/02/2019 1 date_closed 06/05/2019 2 date_incident_reported 03/02/2019 3 date_incident_reported 10/02/2019 3 date_closed 03/03/2019 4 date_incident_reported 14/02/2019 4 date_closed 01/08/2019

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.

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