klangley wrote: If you follow sweul's link, it provides an interesting solution... year(end)*12+month(end)-year(Start)*12+month(Start) The problem is 4/1/12 - 3/31/12 will result in 1 month. just as 3/31/12 - 2/1/12 will result in one month. If that's what you need then use this. |
Brian Garside wrote: This returned 43 months for dates 10/1/2012 - 9/30/2014 using the above Exp If I removed the * 12 it returns 21 which is close. While the DateDiff = 23 which is correct. so SQL wins this one. Far superior to QV date functions |
I agree that QlikView's handling of dates is rather poor. That said, QlikView can't be blamed for sign errors (+month(Start)) or eliminating a necessary term from the expression (*12). The expression should be:
year(End)*12+month(End)-year(Start)*12-month(Start)
Though I'd write it:
(year(End)-year(Start))*12+(month(End)-month(Start))
And yes, I know I could eliminate a pair of parentheses. Either expression should be the equivalent of datediff() for months - they count the month boundaries crossed. You are correct that 4/1/12-3/31/12 = 4/30/12-3/1/12 = 1 month. That's what datediff() does. But I agree that it might not match someone's requirement.
There are two other ways of counting months that I think would be common. First, you might want the time period rounded to the number of months. That's simple enough, though you'll probably never remember the magic number for the average number of days in a month. Fortunately, there's always wikipedia.
round((End-Start)/30.436875)
And second, you might want the number of months in the range. So for 4/1/12-3/31/12 you'd want two months, both March and April. That's simply the original expression +1, unless your start can come after your end, in which case you'd have to subtract 1 for that, assuming you wanted to see negative months in that case. So perhaps:
year(End)*12+month(End)-year(Start)*12-month(Start)+if(Start<=End,1,-1)
All expressions untested, so I may have made silly errors myself.
As I said, I agree that QlikView's handling of dates is poor. We shouldn't have to do any of this. Earlier this year I wrote a suite of date difference functions in COBOL - days, weeks, months, quarters or years like datediff(), or datediff()+/-1, or rounded. The code where everything happens is less than 100 lines. If I can write a full suite of date difference functions in less than 100 lines of COBOL, I can't understand why QlikTech can't put something together.
Maybe there's already something written for the QlikView Components project, or maybe a full suite of date difference functions could be added. I haven't looked at it in a long time, so I'm not sure what's available these days.
http://code.google.com/p/qlikview-components/
