topic Re: Months Between 2 dates in QlikView

Months Between 2 dates

bronsonelliott — Fri, 10 Jan 2014 16:18:40 GMT

I'm trying to calculate the months between 2 dates similar to the Oracle 'MONTHS_BETWEEN' function (MONTHS_BETWEEN)

I've seen a few examples in the forums similar to this:

=Num#(Year(Date1)&Month(Date1))-Num#(Year(Date2)&Month(Date2))

However, this isn't performing the way I'm needing. For example, if I feed it a start date of 1/15/2012 and an end date of 2/14/2012, it returns a value of '1'. I would expect it to return 0.

Here's what I'm trying to accomplish, if the start date is 1/15/2012 and the end date is 2/14/2012, the months between would be zero. However, if the dates are 1/15/2012 and 2/15/2012, then the months between would be 1.

I hope this makes sense. Any help is appreciated.

Thanks!

Re: Months Between 2 dates

Mon, 13 Jan 2014 14:13:37 GMT

Hi Elliott

Doi you want just generate dates for a calendar table or do you need to feed blanks between 2 dates but many times in the same table?

Best regards

Chris

Re: Months Between 2 dates

Mon, 13 Jan 2014 14:31:45 GMT

Hi,

A possible way:

= (year(Date2)-year(Date1))*12 + num(month(Date2)) - num(month(Date1)) + (day(Date2)<day(Date1))

Counting the number of monthes and eventually adding a boolean of -1.

Re: Months Between 2 dates

Mon, 13 Jan 2014 14:39:50 GMT

Hi Bronson Elliott,

You can try this.

Floor(num(BigggerDate-SmallerDate)/30,1,1)

This gives you the number of months between two dates. One month is taken as 30 Days. If you need to check some exceptional dates condition, you can add in if statement.

Re: Months Between 2 dates

bronsonelliott — Mon, 13 Jan 2014 19:42:04 GMT

Thank you Pierre, this seems to be working like I'm needing it too.