
How Do I Get the Number of Months Between Two Dates?
Rakesh Mehta Feb 17, 2012 8:11 PM (in response to Jonathan Shaltz)
Re: How Do I Get the Number of Months Between Two Dates?
Jonathan Shaltz Feb 20, 2012 10:37 AM (in response to Rakesh Mehta )The EDate() and LDate() functions, useful though they are in Excel, do not exist in QlikView.


How Do I Get the Number of Months Between Two Dates?
Gordon Savage Feb 18, 2012 4:52 AM (in response to Jonathan Shaltz)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

Re: How Do I Get the Number of Months Between Two Dates?
Jonathan Shaltz Feb 20, 2012 10:40 AM (in response to Gordon Savage)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.


How Do I Get the Number of Months Between Two Dates?
Michael Solomovich Feb 18, 2012 10:33 AM (in response to Jonathan Shaltz)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?
Jonathan Shaltz Feb 20, 2012 10:44 AM (in response to Michael Solomovich )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?
Rob Wunderlich Feb 20, 2012 4:27 AM (in response to Jonathan Shaltz)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?
Sunil Chauhan Feb 20, 2012 4:32 AM (in response to Rob Wunderlich )ue below code
Interval(Today()  [Set up date], 'mm')

Re: How Do I Get the Number of Months Between Two Dates?
Jonathan Shaltz Feb 20, 2012 11:21 AM (in response to Sunil Chauhan)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?
Michael Solomovich Feb 20, 2012 11:30 AM (in response to Jonathan Shaltz)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?
Jonathan Shaltz Feb 20, 2012 11:40 AM (in response to Michael Solomovich )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 "YYMMDD", "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?
Jonathan Shaltz Feb 20, 2012 12:09 PM (in response to Rob Wunderlich )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('20000101', '20120220') = 145
Too bad QlikView doesn't support method overloading, or we could have a singleparam version which used Today() for the second parameter implicitly.

Re: How Do I Get the Number of Months Between Two Dates?
Rob Wunderlich Feb 21, 2012 2:44 AM (in response to Jonathan Shaltz)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



Re: How Do I Get the Number of Months Between Two Dates?
patrickonline Feb 21, 2012 6:05 AM (in response to Jonathan Shaltz)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?
Jonathan Shaltz Feb 21, 2012 10:32 AM (in response to patrickonline )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?
patrickonline Feb 21, 2012 10:46 AM (in response to Jonathan Shaltz)In that case I would use monthstart for both dates

Re: How Do I Get the Number of Months Between Two Dates?
Jonathan Shaltz Feb 21, 2012 11:10 AM (in response to patrickonline )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?
Rob Wunderlich Feb 21, 2012 1:43 PM (in response to Jonathan Shaltz)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?
rajendran Feb 21, 2012 2:22 PM (in response to Rob Wunderlich )Excellent
On Feb 22, 2012 12:14 AM, "Rob Wunderlich" <

Re: How Do I Get the Number of Months Between Two Dates?
Brian Garside Oct 18, 2012 3:29 PM (in response to Rob Wunderlich )Is there a way to accommodate two date fields with this solution ?
I.e. StartDate  EndDate ?

Re: How Do I Get the Number of Months Between Two Dates?
klangley Oct 18, 2012 3:33 PM (in response to Brian Garside)Look at the entry I left on your original post.





