topic Num years in decimal in QlikView

Num years in decimal

Tue, 08 Sep 2009 17:06:10 GMT

Hi,

How is it possible to get the number of years between 2 days to also include the decimal amount?

e.g. i have 2 dates. 02/09/1999 and 31/08/2009.

The number of days between these dates is 3651. and if I divide this by 365 I get 10.0027397260274 which is incorrect as it does not take into consideration the leap years.The correct value should be 9.99462365359067

Other development tools you can do a monthsbetween function and simply divide by 12 but this is not available in Qlikview!!

Any ideas?

Thanks,
Kevin.

Num years in decimal

Tue, 08 Sep 2009 18:32:57 GMT

If you need the (integer) number of years between two dates, the age() function should do the job.

Num years in decimal

Tue, 08 Sep 2009 19:54:01 GMT

Hi,

As I've stated above is that I need the real (decimal) value not the integer value.

This is why I put the post up.

Cheers,
Kevin.

Num years in decimal

johnw — Wed, 09 Sep 2009 05:52:52 GMT

This comes up often enough that it would be very nice if QlikView would add a monthsbetween() function. Until then, I use this for monthsbetween(date1,date2):

= year(date2)*12 + month(date2) - year(date1)*12 - month(date1)
+ day(date2) / (monthstart(date2,1) - monthstart(date2))
- (day(date1)-1) / (monthstart(date1,1) - monthstart(date1))

Divide by 12 for the years.

That said, I'm calculating the number INCLUDING both dates, for 3652 days, not 3651 days. So I'm getting 9.9972222222 instead of 9.9946235. And you can't just subtract a day out, because it can matter which month and year that day is in. Still, it shouldn't be too hard to modify the expression if you're sure you want to exclude one of the dates, or count it as going from noon to noon on those dates, or whatever you want.

Num years in decimal

Wed, 09 Sep 2009 20:43:23 GMT

Hi John,

Thats exactly what I needed thanks.

Kevin.