Set analysis condition with variable in modifier

[#1]

Hi all,

Can anyone provide an intuitive example of using variables in set analysis.

I know something like this works Sum  ({1< RollingMonthNum = {'$(vRollingCurentMonth)'}>} forcast) however if I wanted to get the sum of forecast for last 6 months based on my variable vRollingCurrentMonth how do I do it?

Example

RollingMonth	RollingMonthNum
Mar-2013	41334
Feb-2013	41306
Jan-2013	41275
Dec-2012	41244
Nov-2012	41214
Oct-2012	41183
Sep-2012	41153
Aug-2012	41122
Jul-2012	41091
Jun-2012	41061
May-2012	41030
Apr-2012	41000
Mar-2012	40969
Feb-2012	40940
Jan-2012	40909
Dec-2011	40878
Nov-2011	40848

How can use set analysis to get a sum of past 6 months if my current variable vRollingCurrentMonth equals 41183.

Thanks,

G

[MayilVahanan]

HI

Try like this

Sum  ({1< RollingMonthNum = {'>=$(=max(MonthNum))<=$(=Num(AddMonths(max(MonthNum),-5)))'}>} forcast)

Edit:

it calculate the forcast value from maximum of monthnum ..if u select the oct-2012, (i.e)41183.  then its calculate range from >=41183 to <= 41030

Hope it helps

[#3]

Hi Mayil,

This works however by default it will show values based on current selection of the RollingMonth field and not the variable vRollingCurrentMonth. Is there a way to use the variable in the set analysis?

G

[MayilVahanan · Accepted solution]

HI

Try like this

Sum  ({1< RollingMonthNum = {'>=$(=vRollingCurrentMonth)<=$(=Num(AddMonths(vRollingCurrentMonth,-5)))'}>} forcast)

Hope it helps

[#5]

Beautiful!

Thank you!

G