How to calculate days since last event

[#1]

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

[swuehl]

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'

[Anonymous]

Maybe something like this :

     =  today() -max ( {< [Number of Incidents]={'1'} >} MakeDate ( Year , Month ) )

[Anonymous · Accepted solution]

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?

[jagan]

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.

[#6]

This worked like a charm.  Thank you.

[#7]

Nice one!