QlikView documentation and resources.
The script below calculates the months difference between two given dates.
//Function to calculate Months Difference in script
SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);
Now, we can use the MonthDiff as a function
($(MonthDiff(StartDate, EndDate)) AS MonthsDifference
In the above load statement we are calculating the months difference between two dates and arriving it as a new column.
We can also use the MonthDiff in expressions also like below
=$(MonthDiff(Today(), Today() + 65))
Hope this helps others.
This was extremely helpful. Thanks
Thats great but we have to take care about the day. For instance, if (2014-03-06) - (2014-01-02) the answer is 3, but if the StarDate have a day greater than the EndDate, we may have some problems.
This is one of the functions that QV need to integrate.
Then use this
Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + If(Day($1) < Day($2), 0, 1) )
currently, it adds "1" to the age of the date, where it should be 0. for instance, when you select a date that is exactly the same as *StarDate* it gives back '1' instead of 0.
This is caused by the part '+1 ));'
when you erase this part, it will show you the correct age in months.
we are getting the difference of months, then to get the Month Names of relevant years...
how i can link to a table...Help me on this..
I am not sure what you mean by this, could you please eleborate?
Note that a DateDiff function is now available in the QlikView Components library http://qlikviewcomponents.org.
What if i would like to work out the date within the month only?
Store From = 5/05/2015
Store To = 25/07/2015
to work out the total datediff would be "Store To - Store From" which will give me total of 61 Days
but what if i would like to know the number of storage days in May which should be 26
June, which should be 30
and the number of storage days in July which should be 25?
also how my i deal with situations where "Store To" date does not exist?
The function is actually great and works correct but I could not understand how the '$1' and '$2' represent the arguments to the function?
Yes, those are arguments to the function.