Last 13 Months Rolling

[narband2778]

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

[sunny_talwar]

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

[narband2778]

Thnaks Sunny, But didn't Work

[narband2778]

Hi Sunny,

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

[sunny_talwar]

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

[narband2778]

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

[sunny_talwar]

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.