topic Difference between 2 dates in QlikView

Difference between 2 dates

Tue, 21 Jul 2009 23:19:16 GMT

Hi there,

How do you compute the difference between two date to get an answer in days or months ?

I'm sure there is a quick function to do that but can't manage to use it ?

What must be the date format of the inputs ?

Thanks for your replies !

Difference between 2 dates

Wed, 22 Jul 2009 01:44:03 GMT

The days btwn is pretty easy. Just date2-date1. The months btwn is a little more complicated. Here is one way that I've done it: (see attached file)

Difference between 2 dates

johnw — Wed, 22 Jul 2009 02:17:42 GMT

And if you need the fraction of months between, it's even MORE complicated. I know I wrote it before, but I don't think I saved it, and it was before they changed the forums.

If I remember, the expression had three parts. One to compute the days from the first day to the end of its month, then divide by the number of days in its month. One to compute the whole months between the months of the dates. And one to compute the days from the last day to the start of its month and divide by the days . Something along those lines. I can probably throw it together if that isn't enough of a hint. I don't think it required any tweaks for the two days in one month, but you'd want to test that the expression still worked for that.

Difference between 2 dates

Wed, 22 Jul 2009 03:20:29 GMT

try

interval

see ref guide for interval and interval#

Talha

Difference between 2 dates

Anonymous — Wed, 22 Jul 2009 04:11:37 GMT

Hi,

if you are fetching data from a sql server you can also think using datediff() sql in the load statements.

Luca C.

Difference between 2 dates

Anonymous — Wed, 22 Jul 2009 07:38:49 GMT

Interval function with 'dd' format is good for days. Speaking of months, it depends on the exact definitions. I had to calculate interval in months only once in my QV career, and the client wanted something very straightforward, like:
(number of days) / 30.5