Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Num years in decimal

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.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Num years in decimal

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.

4 Replies
Not applicable

Num years in decimal

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

Not applicable

Num years in decimal

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.

Highlighted
MVP
MVP

Num years in decimal

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.

Not applicable

Num years in decimal

Hi John,

Thats exactly what I needed thanks.

Kevin.

Community Browser