topic Re: Last 13 Months Rolling in QlikView

Last 13 Months Rolling

narband2778 — Wed, 09 Sep 2015 20:42:40 GMT

Hi Qliks,

I have created a variable for last 13 months (i.e; from 01/08/2014 to 31/08/2015) as current Month = Sep 2015.

vMaxDateCalculated =Date(monthend(today(),-1))

vRolling13Months = '{1<Date ={">=' &date(addmonths(vMaxDateCalculated,-13)+1) & '<=' & vMaxDateCalculated & '"}>}'

ex: Sum($(vRolling13Months)Sales) -- Giving me a right value

But I am unable to get desired result when I am trying to use the same variable with set analysis as shown below.

Ex: Sum($(vRolling13Months){$<Region ={'USA'}>}Sales)

can anyone help me out with this

Kind Regards,

Naresh

Re: Last 13 Months Rolling

sunny_talwar — Wed, 09 Sep 2015 20:50:47 GMT

Have not tried this, but may be give this a try:

vRolling13Months = '{1<Date ={">=' &date(addmonths(vMaxDateCalculated,-13)+1) & '<=' & vMaxDateCalculated & '"}'

1) Sum($(vRolling13Months)>}Sales)

2) Sum($(vRolling13Months)*{$<Region ={'USA'}>}Sales)

May give you syntax error, but might just work

Re: Last 13 Months Rolling

narband2778 — Wed, 09 Sep 2015 20:56:59 GMT

Thnaks Sunny, But didn't Work

Re: Last 13 Months Rolling

narband2778 — Wed, 09 Sep 2015 21:08:56 GMT

Hi Sunny,

I am getting syntax errors... but It is not showing up with desired values.

Re: Last 13 Months Rolling

sunny_talwar — Wed, 09 Sep 2015 21:14:08 GMT

Is it showing any value at all?? or is it showing error?

Re: Last 13 Months Rolling

narband2778 — Wed, 09 Sep 2015 21:16:55 GMT

Hi Sunny,

It is showing an error.

I tried with the below with If statement and it worked but not with set analysis

Sum($(vRolling13Months) if(Region ='USA', Sales))

Thanks,

Naresh

Re: Last 13 Months Rolling

sunny_talwar — Wed, 09 Sep 2015 21:19:13 GMT

Yes the problem is that you variable is closing the first set analysis statement with >} and you are then starting another one {< for region and I don't think you can have two separate set analysis statement for a single expression.