Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
johnw
Champion III
Champion III

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.

View solution in original post

4 Replies
Not applicable
Author

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

Not applicable
Author

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.

johnw
Champion III
Champion III

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
Author

Hi John,

Thats exactly what I needed thanks.

Kevin.