topic Re: Variables in Set Analysis in QlikView

Variables in Set Analysis

Tue, 08 Jul 2014 08:53:47 GMT

My expression

Sum({$<Debug={'Budget'}, MonthSort = {'<=3'}>} If(AC2='70', BalanceLC))

works like a charm, summing up all values where the month is smaller than 3. Now I would like to replace the 3 with a variable, but I can't get it to work.

In the load script I have defined

LET vPeriods = 3;

Now when I try to replace the 3 with vPeriods it doesn't show any values. How can I use the variable in this expression?

Using CTRL+ALT+V shows that the variable exists and has the proper value:

I have tried:

Sum({$<Debug={'Budget'}, MonthSort = {'<=vPeriods'}>} If(AC2='70', BalanceLC)) = All values below limit

Sum({$<Debug={'Budget'}, MonthSort = {'<='&$(vPeriods)}>} If(AC2='70', BalanceLC)) = No data to display

Sum({$<Debug={'Budget'}, MonthSort = {'<='$(vPeriods)}>} If(AC2='70', BalanceLC)) = No data to display

Sum({$<Debug={'Budget'}, MonthSort = {$('<='&vPeriods)}>} If(AC2='70', BalanceLC)) = All values below limit

Sum({$<Debug={'Budget'}, MonthSort = {"<=vPeriods"}>} If(AC2='70', BalanceLC)) = All values below limit

What am I doing wrong?

The following is used to create the field MonthSort (cos our Fiscal Year starts in April):

LOAD * INLINE

[

MonthName, MonthSort, FMonth

APR, 1, 01

MAY, 2, 02

JUN, 3, 03

JUL, 4, 04

AUG, 5, 05

SEP, 6, 06

OCT, 7, 07

NOV, 8, 08

DEC, 9, 09

JAN, 10, 10

FEB, 11, 11

MAR, 12, 12

];

Re: Variables in Set Analysis

MK_QSL — Tue, 08 Jul 2014 08:54:51 GMT

Instead this, better if you can provide some sample data... easy to work and answer

Re: Variables in Set Analysis

sasikanth — Tue, 08 Jul 2014 09:08:25 GMT

Try some thing like

Sum({$<Debug={'Budget'}, MonthSort = {'<$(= vPeriods)'}>} If(AC2='70', BalanceLC)

Re: Variables in Set Analysis

noman212 — Tue, 08 Jul 2014 09:16:17 GMT

Dear Matthias

please refer this link

http://community.qlik.com/blogs/qlikviewdesignblog/2013/11/04/the-magic-of-variables

Re: Variables in Set Analysis

Tue, 08 Jul 2014 09:16:18 GMT

Sasikanth_Narne

Thanks, this got me on the right track.

Sum({$<Debug={'Budget'}, MonthSort = {'<=$(=vPeriods)>'}>} If(AC2='70', BalanceLC))

seems to do the trick

Re: Variables in Set Analysis

sasikanth — Tue, 08 Jul 2014 09:24:05 GMT

u r welcome