Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I need some help. I need to calculate the number of days since the last incident recorded. My data looks something like this. I have people filing out these forms on a monthly basis and just need to show the number of days since there was an incident. Any help would be much appreciated.
Segment | Month | Year | Number of Incidents |
---|---|---|---|
CORP | 4 | 2015 | 1 |
CORP | 5 | 2015 | 0 |
CORP | 6 | 2015 | 0 |
CORP | 7 | 2015 | 0 |
CORP | 8 | 2015 | 0 |
CORP | 9 | 2015 | 0 |
In the script:
OriginalData:
Load
*,
MakeDate (IncidentYear,IncidentMonth,1) as IncidentMonthDate;
Load * Inline [
Segment, IncidentMonth, IncidentYear, NoOfIncidents
CORP,4,2015,1
CORP,5,2015,0
CORP,6,2015,0
CORP,7,2015,1
CORP,8,2015,0
CORP,9,2015,0
];
MostRecentIncident:
Load
Segment as MaxIncidentSegment
,max (IncidentMonthDate) as MaxIncidentMonthDate
,Today () - max (IncidentMonthDate) as DaysSinceMaxIncident
Resident OriginalData
Where NoOfIncidents > 0
Group by Segment
;
This will return the number of days since the last incident (sort of; since your data is missing the day of the month, the first is assumed). If the number of incidents is equal to 0, the month is ignored.
Were you instead looking for the number of days since the prior incident?
I would start with creating a date field in the data model made of year and month:
LOAD *,
MakeDate(Year, Month) as Monthstart;
LOAD Segment,
Month,
Year,
[Number of Incidents]
FROM
[https://community.qlik.com/thread/189662]
(html, codepage is 1252, embedded labels, table is @1);
Then the days since last incident can be calculated in a text box like:
=Interval(Today()-(Max({<[Number of Incidents] = {">0"}>} Monthstart)),'D') & ' days'
Maybe something like this :
= today() -max ( {< [Number of Incidents]={'1'} >} MakeDate ( Year , Month ) )
In the script:
OriginalData:
Load
*,
MakeDate (IncidentYear,IncidentMonth,1) as IncidentMonthDate;
Load * Inline [
Segment, IncidentMonth, IncidentYear, NoOfIncidents
CORP,4,2015,1
CORP,5,2015,0
CORP,6,2015,0
CORP,7,2015,1
CORP,8,2015,0
CORP,9,2015,0
];
MostRecentIncident:
Load
Segment as MaxIncidentSegment
,max (IncidentMonthDate) as MaxIncidentMonthDate
,Today () - max (IncidentMonthDate) as DaysSinceMaxIncident
Resident OriginalData
Where NoOfIncidents > 0
Group by Segment
;
This will return the number of days since the last incident (sort of; since your data is missing the day of the month, the first is assumed). If the number of incidents is equal to 0, the month is ignored.
Were you instead looking for the number of days since the prior incident?
Hi,
Try like this in script
LOAD
Num(Today() - LastEventDate) AS Days
LOAd
Segment,
Month,
Year,
[Number of Incidents] ,
MakeDate(Year, Month) AS LastEventDate
FROM DataSource;
Now use below expression
=Only({<LastEventDate={"$(=Max({<[Number of Incidents]={'>0'}>}LastEventDate))"}>} Days)
Hope this helps you.
Regards,
Jagan.
This worked like a charm. Thank you.
Nice one!