QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Contributor II

number of months in a variable

HI All,

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 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 )

Rj

1 Solution

Accepted Solutions
Honored Contributor III

Re: number of months in a variable

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

3 Replies
Honored Contributor III

Re: number of months in a variable

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

Contributor II

Re: number of months in a variable

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))));

Rj

Honored Contributor III

Re: number of months in a variable

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.