CODEWITHSUNDEEP
excelscriptingsasreportingqlikview
DATAMART
DATAMART

Reputation: 29

Point in time calculation

I have a challenge that and unsure how to tackle it. I have some call centre data like this 

Incident number    Received date    Closed Date
-----------------------------------------------
  111               01/01/2020      01/06/2020
  222               01/04/2020      01/09/2020
  333               01/05/2020      01/10/2020
  444               01/07/2020

What I want to do i point in time reporting for  each month in 2020 what was the average age of open incidents.  So for example in 2020 month  one the count will be only one as there has been one incident raised in 2020 01 and it was still open in 2020 01. By month 5 the count will be 3 as none of the incidents have closed yet, however by month 6 the count will be 2 as we now have a claim closed in month 6.  The incidents that remain open will be counted for every month after the created date until the closed date. It would have to be scripted but not sure how to tackle this. Any ideas?

Tools available: QlikView, SAS, Excel

Upvotes: 0

Views: 299

Answers (1)

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11978

enter image description here

Formula to count incidents:

=COUNTIFS($B$2:$B$5;"<="&EDATE(E3;1)-1)-COUNTIFS($C$2:$C$5;"<="&EDATE(E3;1)-1)

We use EDATE to get exactly 1 month ahead of current one, and minus one, so we get always the last day of the current month, even if it has 28, 29, 30 o 31 days.

With COUNTIF you count total incidents opened before last day of current month minus total closed incidents before last day of current month.

The value of the Month columns must be first day of each month but thanks to format, you can format the cells to look like just the month (but the value will be always a day!!!) so it looks more professional:

enter image description here

Aplying format mmm yy to selection make it looks better (just my opinion)

Upvotes: 1

Related Questions