topic Re: difference b/w the 2 dates in years in QlikView

difference b/w the 2 dates in years

PradeepReddy — Tue, 13 May 2014 10:42:01 GMT

Hi All,

I want to caluclate the difference b/w the 2 dates and the result should be in years. So that I can create the bucket as shown below. I am able to calulate this in no.of days, after that based on the days range i am doing the calculation. But the tricky part is there might be chance of Leap years. So how can I handle this scenario.

Bucket:

<1 yr

1-5 yrs

6-10 yrs

>10 yrs

Expression I used:

IF(TODAY()-REGISTRATION_DATE < 365,'< 1',
IF(TODAY()-REGISTRATION_DATE >= 365 AND TODAY()-REGISTRATION_DATE <= 1825,'1 - 5',

IF(TODAY()-REGISTRATION_DATE > 1825 AND TODAY()-REGISTRATION_DAT) <= 3650,'6 - 10',

IF(TODAY()-REGISTRATION_DATE > 3650,'> 10')))) AS TEMP_DATE_RANGE

Thanks in advance.

Pradeep

Re: difference b/w the 2 dates in years

SunilChauhan — Tue, 13 May 2014 10:47:43 GMT

use below code

IF(Interval(TODAY()-REGISTRATION_DATE ,'YYYY')< 1,'< 1',
IF(Interval(TODAY()-REGISTRATION_DATE ,'YYYY') >= 1 AND Interval(TODAY()-REGISTRATION_DATE ,'YYYY')) <= 5,'1 - 5',

IF(Interval(TODAY()-REGISTRATION_DATE ,'YYYY') > 6 AND Interval(TODAY()-REGISTRATION_DATE ,'YYYY')) <= 10 ,'6 - 10',

IF(Interval(TODAY()-REGISTRATION_DATE ,'YYYY') > 10,'> 10')))) AS TEMP_DATE_RANGE

hope this helps

Re: difference b/w the 2 dates in years

SunilChauhan — Tue, 13 May 2014 10:49:27 GMT

or you can use

Year(TODAY())-Year(REGISTRATION_DATE)

age(TODAY, REGISTRATION_DATE) make sure format of both today and REGISTRATION_DATEshould be same

inplace of

TODAY()-REGISTRATION_DATE

in expression

hope this helps

Re: difference b/w the 2 dates in years

maxgro — Tue, 13 May 2014 10:51:31 GMT

perhaps you can use age function

age(timestamp, date_of_birth)

Returns the age at the time of timestamp (in completed years) of somebody born on date_of_birth.

Examples:

age('2007-01-25', '2005-10-29') returns 1

age('2007-10-29', '2005-10-29') returns 2

Re: difference b/w the 2 dates in years

PradeepReddy — Tue, 13 May 2014 11:01:35 GMT

TODAY()) - Year(TODAY()-365) and (TODAY()) - Year(TODAY()-366) returning the value as '1'.

For the first expression it should not be '1', as the difference is not more than one year.

Re: difference b/w the 2 dates in years

Roop — Tue, 13 May 2014 11:05:05 GMT

You can either use Massimo Grossi's approach but the reality of the situation is that it is going to make very little difference in overall scope. Obviously, there are at least 1 leap year in the 5 year category and at least 1 in the 6 to 10 year category so those numbers can be adjusted slightly.

What I would difinitely do is to set a RangeNumber instead of descriptors. This is then linked to the inline table set out below. This means that you are able to change descriptions easily and perhaps more importantly sort the list in a listbar.

In line load for RangeNumber

AgeRange:

Load * Inline

[RangeNumber, Bucket

1, < 1 year

2, 1 to 5 year

3, 6 to 10 year

4, >10 yrs

];

Hope this helps

Re: difference b/w the 2 dates in years

SunilChauhan — Tue, 13 May 2014 11:06:51 GMT

so what result should you expect for this cases

i mean fr below

TODAY()) - Year(TODAY()-365) and (TODAY()) - Year(TODAY()-366) returning the value as '1'.

For the first expression it should not be '1', as the difference is not more than one year.

Re: difference b/w the 2 dates in years

PradeepReddy — Tue, 13 May 2014 11:11:29 GMT

I am expecting the following results....

Year(TODAY()) - Year(TODAY()-364) = 0

Year(TODAY()) - Year(TODAY()-366) = 1;

but both expressions giving the value as '1'.

Re: difference b/w the 2 dates in years

SunilChauhan — Tue, 13 May 2014 11:14:09 GMT

in that case use below

age(today(),(today()-364))

age(today(),(today()-366))

hope this hellps

Re: difference b/w the 2 dates in years

Roop — Tue, 13 May 2014 11:14:47 GMT

They will both return 2014 and 2013 respectively and so will return 1