topic Re: Set analysis condition with variable in modifier in QlikView

Set analysis condition with variable in modifier

Mon, 22 Oct 2012 21:06:23 GMT

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,

Re: Set analysis condition with variable in modifier

MayilVahanan — Mon, 22 Oct 2012 21:18:49 GMT

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

Re: Set analysis condition with variable in modifier

Mon, 22 Oct 2012 21:34:12 GMT

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?

Re: Set analysis condition with variable in modifier

MayilVahanan — Mon, 22 Oct 2012 21:40:40 GMT

Try like this

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

Hope it helps

Re: Set analysis condition with variable in modifier

Mon, 22 Oct 2012 21:43:27 GMT

Beautiful!

Thank you!