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)
then just use it in the script eg
MonthDiff(text(W1DEL1), text(today()))) * 12))
I never did find an easier way myself!
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.
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]))
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?
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.
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.
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)));
=$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.
You can provide for an optional second parameter with a default of today like this:
) - ((year($1)*12)+month($1));
I'll leave it to you to improve the parens
I am still lost in your expression. being New to QV & Scripting but is been like thrown into the deep end.
Data set i have:
Start Date Completed Date 21-Mar-1997 31-Dec-2030 01-Jun-2010 31-Dec-2030 10-May-2013 31-Dec-2030
- find the number of months between the dates
- find the number of Years between the dates
- Do not load/(drop) data below certain year stamp
if(Left([Contract No],3)='FPA','FPA','CONTRACT') as TYPE,
SET MonthDiff=((year[Completed Date])*12)+month[Completed Date])) - (((year([Start Date])*12)+month([Start Date]))),
Month([Start Date]) as [Start Month],
Year( [Start Date]) as [Start Year],
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.
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.