topic Re: How Do I Get the Number of Months Between Two Dates? in QlikView

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

Sat, 18 Feb 2012 00:01:32 GMT

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!

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

disqr_rm — Sat, 18 Feb 2012 01:11:30 GMT

You should be able to do the same formula in qlikview.

http://support.microsoft.com/kb/214134

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

Sat, 18 Feb 2012 09:52:57 GMT

You could always create your own custom function in the module:

Function MonthDiff (startdate, enddate)

' return number of months between two dates

If IsDate(startdate) and IsDate(enddate) Then

MonthDiff = DateDiff("m", startdate, enddate)

End If

End Function

then just use it in the script eg

MonthDiff(text(W1DEL1), text(today()))) * 12))

I never did find an easier way myself!

Regards,

Gordon

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

Anonymous — Sat, 18 Feb 2012 15:33:13 GMT

Jonathan,

Since "Month" is not a fixed length interval, you have to define a rule. For practical purposes, this can be a reasonable approximation:

round((interval#(Today()-[Set up date], 'DD')/30.5), 0.1)

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

rwunderlich — Mon, 20 Feb 2012 09:27:57 GMT

Sorry to shake your faith in QV , but the expression I would use is:

=((year(today(2))*12)+month(today(2))) - (((year([Set up date])*12)+month([Set up date])))

You can ease your pain somewhat by creating a variable function in the script:

SET MonthDiff=((year(today(2))*12)+month(today(2))) - (((year($1)*12)+month($1)));

and then in the chart:

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

-Rob

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

SunilChauhan — Mon, 20 Feb 2012 09:32:30 GMT

ue below code

Interval(Today() - [Set up date], 'mm')

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

Mon, 20 Feb 2012 15:37:06 GMT

The EDate() and LDate() functions, useful though they are in Excel, do not exist in QlikView.

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

Mon, 20 Feb 2012 15:40:12 GMT

I'm specifically looking for an expression in the QlikView document itself (e.g., a dimension in a pivot table), not the script. There are over a dozen date metrics in the data set, so it would be impractical to calculate all possible date differences in the script.

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

Mon, 20 Feb 2012 15:44:40 GMT

The length of months differs, but the number of months between two dates is defined quite consistently across most or all RDMSs: the number of month boundaries crossed from A to B. 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. Taking the number of days and dividing it by 30.5 would give inaccurate results.

Using 30.438333 as a divisor would be close over the long term, but would still report 03/01 to 03/31 as being a period of one month, against this business need and (IMHO) against most people's intuitive sense.

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

Mon, 20 Feb 2012 16:21:58 GMT

Tried that. As reported above, it gave a value which was much too high. Expanding on the example, if I use this formula:

='From ' & Text(Today())

& ' to '

& Text(Date#([Set up date]))

& ' is '

& Interval#(Today() - Date#([Set up date]), 'MM')

& ' months'

...I get values like this:

From 2012/02/20 to 2006/03/10 is 2173 months

From 2012/02/20 to 2006/11/13 is 1925 months

From 2012/02/20 to 2006/11/14 is 1924 months

From 2012/02/20 to 2006/04/04 is 2148 months

From 2012/02/20 to 2008/02/17 is 1464 months

From 2012/02/20 to 2007/12/04 is 1539 months

From 2012/02/20 to 2007/09/04 is 1630 months

From 2012/02/20 to 2007/12/14 is 1529 months

From 2012/02/20 to 2008/01/11 is 1501 months

From 2012/02/20 to 2008/02/13 is 1468 months

From 2012/02/20 to 2008/03/21 is 1431 months

So it would appear that the 'MM' parameter in Interval#() gives days. How this could possibly be a good idea, I do not know.

Even more bizzarely, the simpler expression "=Interval#(Today() - Date([Set up date]), 'MM')" returns 2,617 in every row, even when the more elaborate expression get different numbers from the very same Interval() call.

Is this all an experiment by the Great Old Ones to drive us mad?

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

Anonymous — Mon, 20 Feb 2012 16:30:39 GMT

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

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

Mon, 20 Feb 2012 16:40:51 GMT

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.

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

Mon, 20 Feb 2012 16:47:03 GMT

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.

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

rwunderlich — Tue, 21 Feb 2012 07:44:58 GMT

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

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

Tue, 21 Feb 2012 11:04:33 GMT

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) ;

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

Tue, 21 Feb 2012 15:32:37 GMT

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.

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

Tue, 21 Feb 2012 15:46:33 GMT

In that case I would use monthstart for both dates

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

Tue, 21 Feb 2012 16:10:42 GMT

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.

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

rwunderlich — Tue, 21 Feb 2012 18:43:59 GMT

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

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

Tue, 21 Feb 2012 19:22:38 GMT

Excellent

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