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 |
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
