topic Re: Interval function in QlikView

Interval function

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

Hey guys,

I am trying to get the amount of months from

1. REGISTERED AND COVER START DATE

2. NOTIFICATION DATE and INCIDENT DATE

I have been using the Interval function to no avail.

Attached is the results that I should get.

The difference between 1. is shown as POL_TO_INC_M and the difference in 2. is show as INC_TO_NOTIFY_M

fabs(Interval(REGISTERED - COVER_START_DATE, 'M') ) as POL_TO_INC_M,
fabs(Interval(NOTIFICATION_DATE - INCIDENT_DATE, 'M')) as INC_TO_NOTIFY_M

Any help appreciated

Re: Interval function

sunny_talwar — Mon, 22 Aug 2016 14:01:50 GMT

Try the expression provided by Rob here:

How Do I Get the Number of Months Between Two Dates?

Re: Interval function

sunny_talwar — Mon, 22 Aug 2016 14:04:36 GMT

May be like this:

(Year(REGISTERED)*12) + Month(REGISTERED)) - (Year(COVER_START_DATE)*12) + Month(COVER_START_DATE)) as POL_TO_INC_M,

(Year(NOTIFICATION_DATE)*12) + Month(NOTIFICATION_DATE)) - (Year(INCIDENT_DATE)*12) + Month(INCIDENT_DATE)) as INC_TO_NOTIFY_M,

Re: Interval function

sunny_talwar — Mon, 22 Aug 2016 14:08:32 GMT

When I use this script:

Table:

LOAD *,

((Year(REGISTERED)*12) + Month(REGISTERED)) - ((Year(COVER_START_DATE)*12) + Month(COVER_START_DATE)) as POL_TO_INC_M,

((Year(NOTIFICATION_DATE)*12) + Month(NOTIFICATION_DATE)) - ((Year(INCIDENT_DATE)*12) + Month(INCIDENT_DATE)) as INC_TO_NOTIFY_M;

LOAD REGISTERED,

COVER_START_DATE,

INCIDENT_DATE,

NOTIFICATION_DATE,

POL_TO_INC_M as POL_TO_INC_M_4M_EXCEL,

INC_TO_NOTIFY_M as INC_TO_NOTIFY_M_4M_EXCEL

FROM

[Test (9).xlsx]

(ooxml, embedded labels, table is Sheet1);

I get this:

Re: Interval function

bobbydave — Mon, 22 Aug 2016 14:31:23 GMT

This works perfect Sunny, but is there a reason why the Interval function wouldn't work?

Re: Interval function

sunny_talwar — Mon, 22 Aug 2016 14:32:50 GMT

Interval function can only output, days hours months and seconds. Unfortunately, it doesn't work for Months and years. If you want the days difference or hours difference, then I would recommend using the Interval function.