4 Replies Latest reply: Sep 9, 2009 4:43 PM by kevinhumphreys RSS

    Num years in decimal


      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?


        • Num years in decimal

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

            • Num years in decimal


              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.


                • Num years in decimal
                  John Witherspoon

                  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.