
Re: Subtract two dates to get the number of months
Stefan Wühl Oct 18, 2012 2:56 PM (in response to Brian Garside)Here is a nice discussion about a similar request:

Re: Subtract two dates to get the number of months
klangley Oct 18, 2012 2:59 PM (in response to Brian Garside)SQL function on you load script:
datediff(mm,StartDate,EndDate)

Re: Subtract two dates to get the number of months
Brian Garside Oct 18, 2012 3:27 PM (in response to klangley)This actually seems to be the simpler solution except it return weird dates. The Dates are stored as M/DD/YYYY
So Im not sure why the SQL function cant return good results


Re: Subtract two dates to get the number of months
klangley Oct 18, 2012 3:27 PM (in response to Brian Garside)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.

Re: Subtract two dates to get the number of months
Brian Garside Oct 18, 2012 3:47 PM (in response to klangley)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

Re: Subtract two dates to get the number of months
klangley Oct 18, 2012 3:50 PM (in response to Brian Garside)I started in SQL environments and prefer using it still, but the more you use Qlikview for clients you will find that you need to work with QVDs which prevents the use of your SQL tricks and need to learn the Qlikview work arounds.

Re: Subtract two dates to get the number of months
Brian Garside Oct 18, 2012 4:05 PM (in response to klangley)True Indeed.
Funny most of the time I do prefer to use QV ETL commands, but I was pleasantly surprised with the power of the SQL date functions. I wish QV would expand that library a bit more.



Re: Subtract two dates to get the number of months
John Witherspoon Oct 18, 2012 8:37 PM (in response to klangley)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)*12month(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/123/31/12 = 4/30/123/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((EndStart)/30.436875)
And second, you might want the number of months in the range. So for 4/1/123/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)*12month(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.

Re: Subtract two dates to get the number of months
Brian Garside Oct 19, 2012 11:25 AM (in response to John Witherspoon )Very informative, as usual.
I will check this out and try the Exp you provided.

