Discussion Board for collaboration on QlikView Scripting.
Thanks for your time ;
do we have a function which returns the number of months from given two dates
I have to divide an expression with the months remaining in fiscal year
LET vFYEnding = DATE('03/31/2016','MM/DD/YYYY');LET vLastMonth = DATE(FLOOR(MONTHEND(ADDMONTHS(TODAY(),-1))),'MM/DD/YYYY');
LET vNoOfMonths = $(vLastMonth) - $(vFYEnding); - this is giving me 8 instead of 7
my fiscal year ending is 31 March 2016 - as of August I have 7 months remaining; as of sep 6 months
my expression - I need to replace 7 with the variable so that it is dynamic
SUM (TCost) + (AVG(TCost) * 7 )
Thanks for your time,
Go to Solution.
If you include August I think it will return 8 from Aug,15 to Mar,16.
Try below script if it suits and also see the link for better way to calculate month diff -
LET vFYEnding = Date(DATE#('03/31/2016','MM/DD/YYYY'),'MM/DD/YYYY');
LET vLastMonth = DATE(FLOOR(MONTHSTART(TODAY())),'MM/DD/YYYY');
//Function to calculate Months Difference in script
SET MonthDiff = Num(((year($2) * 12) + month($2)) - (((year($1) * 12) + month($1))) + 1);
vNoOfMonths=$(MonthDiff(vLastMonth, vFYEnding)); //This returns 7
Calculating Months difference between two dates
Thank you Digvijay - Thanks a lot; appreciate your time
the following is working for me - it is returning 7
Let vTemp = year(vFYEnding)*12)+month(vFYEnding)) - (((year(vLastMonth)*12)+month(vLastMonth))));
Good to hear that it worked for you. I suggest to close/mark the thread as answered to notify community that no more action is required.