topic Re: Expression (set analysis) variable in QlikView

Expression (set analysis) variable

zagzebski — Sat, 30 May 2015 20:17:02 GMT

I tried to set up the following variable:

Let vIncomeMaxYear = 'Sum( {$<[Account Type]={Income}, Calendar_Year={$(=Max(Calendar_Year))}>} [Amount GL])';

But when I load the file the variable comes back as:

Sum( {$<[Account Type]={Income}, Calendar_Year={}>} [Amount GL])

New to creating variables for expressions...

Steve

stabben23 — Sat, 30 May 2015 20:30:10 GMT

Hi, try to use the "search" inside here, like this: Calendar_Year={"$(=Max(Calendar_Year))"}

maxgro — Sat, 30 May 2015 22:12:52 GMT

maybe

let vIncomeMaxYear = replace('Sum( {$<[Account Type]={Income}, Calendar_Year={"^(=Max(Calendar_Year))"}>} [Amount GL])', '^', '$');

or you can load variables from an external file (txt, excel; there are a lot of post on this in the community)

excel is attached; copy the excel in the same dir as your QlikView .qvw

and add this to your QlikView .qvw to load the variables from excel

Directory;

TempVariable:

LOAD Var, Val

FROM [Variables.xlsx]

(ooxml, embedded labels, table is Sheet1);

For i = 0 to NoOfRows('TempVariable')-1

LET vName = Peek('Var', i, 'TempVariable');

LET $(vName) = Peek('Val', i, 'TempVariable');

Next i;

LET i = Null();

LET vName = Null();

Drop Table TempVariable;