Calculating point on time

[Dwyane]

Hi All,

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?

[eddie_wagt]

You will have to script and generate missing values. This could be a solution. You have to adapt it according to your data.

It is a preceding load, read from bottom to up.

// Last load, flag all dates where the incidents are still open, yearmonth is normally in your calendar
LOAD    [Incident number]
,		if([Date_Point_in_Time]<[Closed Date],1,0)      as _Flag
,		Date_Point_in_Time
,		Year(Date_Point_in_Time)&num(Month(Date_Point_in_Time),'00') as YearMonth
;
// Make new date point and loop from received date to closed date
LOAD    [Incident number]
,		date(date([Received date]) + iterno()-1)		as Date_Point_in_Time
,		[Received date]
,		[Closed Date]
while date([Received date]) + iterno()-1 <= Date([Closed Date])
;
;
// Load sample data and for dates without closed date, make it today
LOAD [Incident number]
,    [Received date]
,	 if(isnull([Closed Date]) or len([Closed Date])=0, date(today()),[Closed Date]) as [Closed Date]
;
// Load Sample data
LOAD * INLINE [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
];

After loading make a table like this

Kind regards

Eddie

If this answers your question or solves your issue, be sure to mark the answer as correct by clicking 'Accept as Solution'. This will mark the post as solved and other Qlikkies will gravitate towards this post as it as a possible solution for their issue. Multiple responses can be accepted as a solution so make sure to select all that apply.

[vinieme12]

Load 

IncidentNumber

,Date(IncidentDate+ITERNO()-1) as Date

While IncidentDate+ITERNO()-1<= alt(ClosedDate,IncidentDate);

Load IncidentNumber,IncidentDate,ClosedDate 

From yourdataSource;

In chart; just use

count(Distinct IncidentNumber)

[Dwyane]

Thank you. What if i wanted to calculate the average age of incidents by month? So I would have to look at the oldest open incident raised before that month that are still open and also items that were closed before that month