Hi.
I have some incident data that has incident number, date opened and date closed.
Case #1 was Opened January 1st and Closed on
May 23rd
Case #2 was Opened January 1st and Closed on June 15th
Case #3 was Opened on February 10th and is still open
If I were to report on how many cases were "open" at the end of the month from January to July, it would look like this
January - 2 Cases in Backlog (#1 and #2)
February - 3 Cases in Backlog (#1, #2 and #3)
March and April - 3 Cases in Backlog (#1, #2 and #3)
May - 2 Cases in Backlog (#2 and #3, because #1 closed before the end of May)
June and July - 1 Case in Backlog (#3 because #2 closed before the end of June)
For where the incident is not closed I want to be able to count from when the incident was open to that months reporting period. So in March for case 3 I would calculate I would calculate from the 10th of Feb to 31st March.
For each month I would need to work out the average number of days incidents remains open. So looking where a closed date does not exist or where the closed date was after the reporting month. Not really sure how to tackle this without splitting the data out by month which I want to avoid.
So I would need to work out the age of each incident that in scope by either subtracting the closed date by open date or the month end date by the open date, add them up and then divide by number of incidents in scope
Any ideas?
I have some incident data that has incident number, date opened and date closed.
Case #1 was Opened January 1st and Closed on
May 23rd
Case #2 was Opened January 1st and Closed on June 15th
Case #3 was Opened on February 10th and is still open
If I were to report on how many cases were "open" at the end of the month from January to July, it would look like this
January - 2 Cases in Backlog (#1 and #2)
February - 3 Cases in Backlog (#1, #2 and #3)
March and April - 3 Cases in Backlog (#1, #2 and #3)
May - 2 Cases in Backlog (#2 and #3, because #1 closed before the end of May)
June and July - 1 Case in Backlog (#3 because #2 closed before the end of June)
For where the incident is not closed I want to be able to count from when the incident was open to that months reporting period. So in March for case 3 I would calculate I would calculate from the 10th of Feb to 31st March.
For each month I would need to work out the average number of days incidents remains open. So looking where a closed date does not exist or where the closed date was after the reporting month. Not really sure how to tackle this without splitting the data out by month which I want to avoid.
So I would need to work out the age of each incident that in scope by either subtracting the closed date by open date or the month end date by the open date, add them up and then divide by number of incidents in scope
Any ideas?