Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

qvraj123
Contributor II

number of months in a variable

HI All,

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,

Rj

1 Solution

Accepted Solutions
Digvijay_Singh
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
Digvijay_Singh
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

qvraj123
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

Digvijay_Singh
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.

Community Browser