How Do I Get the Number of Months Between Two Dates?

[#1]

Other posts here in the forii have mentioned Interval() to get the number of days or months between two days.  However, I have not found a format code to get months.  Either of these expressions...

=Interval#(Today() - [Set up date], 'YY-MM-DD')

=Interval#(Today() - [Set up date], 'YY-MM')

...give me the number of days since [Set up date], but I need months.  Using 'MM' gives the value "2614" for all dates from 2005 to yesterday - I have no idea where that number comes from.  The doc page for Interval() is of course useless.

If there's a simpler way to get the number of months between two dates, such as is present in any RDMS (e.g., DATEDIFF() in MSSQL and MySQL, MONTHS_BETWEEN() in Oracle), I am all ears.  If someone suggests adding the difference in years times 12 to the difference in months, I fear I may lose any remaining faith in QlikView.

Thank you!

[Anonymous]

Jonathan,

Now you have the rule: "Any day in February to any day in March is one month; any day in March to any other day in March is zero months, etc".  My initial understanding was quite different...

I think it makes sense to assign unique sequence numbers to the months in the master calendar.  It will help to calculate the difference on the front end.

As for the interval# using 'MM" - don't expect it to work.  It accepts DD, hh, mm, and ss, unless there were some recent additions.

Regards,

Michael

[#3]

I was hoping to avoid that, since it adds one more megabyte of data (assuming 50% compression) to an average file.  Since monthly calculations don't come up very often, I'm leaning toward Rob's solution of a

Re: the use of "MM": thanks for spelling that out.  The QV10 doc only gives the examples "YY-MM-DD", "hh:mm:ss", "D hh:mm", but is not explicit about how the codes are interpreted.  It seems the answer is "the number of days between the dates."  Except when it's not.

[#4]

Yeah, my faith is pretty much curled up a corner sobbing by now.  Your solution is very graceful though, and it raises a productive avenue I'll try to keep in mind in the future (UDFs).  The only thing that concerns me is explaining to the users that this method has to be called with the $(...) surround.

The parentheses in your function were making my eyes cross, so I simplified it slightly:

SET MonthDiff=(Year($1)*12 + Month($1) - (Year($2)*12 + Month($2)));

Called as such:

=$(MonthDiff(Today(), [Set up date]))

EDIT: For consistency with similar functions in RDMSs, it would probably be better to express the logic as parameter B - parameter A, or putting it another way, "how many months passed from A to B?".

SET MonthDiff=(Year($2)*12 + Month($2) - (Year($1)*12 + Month($1)));

Results:

=$MonthDiff('2000-01-01', '2012-02-20') = 145

Too bad QlikView doesn't support method overloading, or we could have a single-param version which used Today() for the second parameter implicitly.

[rwunderlich]

You can provide for an optional second parameter with a default of today like this:

SET MonthDiff=(if(len($2)=0

,(year(today(2))*12)+month(today(2))

, (year($2)*12)+month($2))

) - ((year($1)*12)+month($1));

I'll leave it to you to improve the parens

-Rob

http://robwunderlich.com

[#6]

Hi Jonathan,

I always work with the30.4 as divider. This is the average nr of days per month in a year. I did not find any miscalculation so far.

t1:

Load

          *,

          round((EndDate - StartDate)/30.4) as months

          ;

load

          makedate(2012,01,01) as StartDate,

          makedate(2012,2,01) as EndDate

autogenerate (1) ;

[#7]

February 29th to April 1st is 32 days, but two months.  Your calculation would give 1.05, which would round to one.  You cannot calculate the number of months between two days with the number of days between them, just as you cannot calculate the number of days between two dates when given the month and day alone.

[#8]

In that case I would use monthstart for both dates

[#9]

That's close, but it's still an approximation.  A quick test shows that from 2000/03 to 2029/02 is 347 months, but that math gives 348: 10,564 days / 30.4 = 347.5.  That assumes that the rounding function used rounds 0.5 up; if it rounds down, there will be other errors.  Using the more exact figure of 30.438333 helps, but I'd be more comfortable using a calculation which is algorithmically correct rather than risk getting incorrect results at some point.

[rwunderlich]

Here's a somewhat more compact version of my previous solution allowing an oprional second parm using ALT.

SET MonthDiff=

(

(year(alt($2,today(2)))*12) + (month(alt($2,today(2))))

)

- ((year($1)*12)+month($1));

-Rob

[#11]

Excellent

On Feb 22, 2012 12:14 AM, "Rob Wunderlich" <