topic Re: Set Analysis Expression Using Variables in QlikView

Set Analysis Expression Using Variables

Mon, 26 Jan 2026 18:19:17 GMT

Hi guys. I have a question (or two) regarding to variables behaviour on Qlikview

I have two variables with max year and max month from a dates set.

I declared the variables on the script with SET and LET (trying to figure it out why it doesn't work), I have the declaration like this:

SET vMaxYear='Max(YearFromDate)';

SET vMaxYear='=Max(YearFromDate)';

I'm using vMaxYear in a set analysis expression like: SUM({$<Year={$(=vMaxYear)}>} Sales)

I have tried with the equal (in definition, and in the set analysis), without it and have no luck. Strange enough, I defined the variable through Variable Overview window and that way it worked but I think that script it's better place (for reuse and so) to define variables not Variable Overview.

I can attach some example file if you need to, can you point me in the right direction here? I see documentation incomplete in this aspect as it only says SET stores expressions without evaluating them, LET stores values from evaluated expressions (in my understanding)

Thanks in advance

Re: Set Analysis Expression Using Variables

NickHoff — Tue, 10 Feb 2015 22:01:32 GMT

Try this syntax on the variable in the SET {'($(vMaxYear))'}

SUM(({$<Year={'($(vMaxYear))'}>}Sales

Re: Set Analysis Expression Using Variables

Tue, 10 Feb 2015 22:25:55 GMT

Thanks, I already tried that and make no difference, the only way it works is with the variable defined through variable overview. Do you know if Qlikview has any limitation regarding variable complexity in script or so? I don't find variable overview like the way to go having reuse and order in mind

Re: Set Analysis Expression Using Variables

saurabh5 — Tue, 10 Feb 2015 22:30:37 GMT

Hi Cristian,

Can you remove the quation marks

SET vMaxYear=Max(YearFromDate);

see if it works

Re: Set Analysis Expression Using Variables

Peter_Cammaert — Wed, 11 Feb 2015 08:55:24 GMT

If you defined vMaxYear in your Load Script, then what is the value of vMaxYear after the next reload?

Re: Set Analysis Expression Using Variables

Wed, 11 Feb 2015 14:16:03 GMT

Ok guys, I tried your suggestions and have no luck so far

I make this attached qvw to show you the problems I have in a basic scenario. If the attached qvd doesn't work, refer to the qvs file that have the inline tables used. The expressions I tried are:

- SUM({$<YearDate={'2010'}>} ClientNumber) => WORKS (Returns 3500)

- SUM({$<YearDate={'2010'}, MonthDate={'Jun'}>} ClientNumber) => WORKS (Returns 800)

- SUM({$<YearDate={$(vMaxYear)}>} ClientNumber) => Error: Error in set modifier ad hoc element list: ',' or ')' expected

- SUM({$<YearDate={$(vMaxYear)}, MonthDate={$(vMaxMonth)}>} ClientNumber) => Error: Error in set modifier ad hoc element list: ',' or ')' expected

- SUM({$<YearDate={'($(vMaxYear))'}>} ClientNumber) => WORKS (Returns 0)

//Variable without quotes

- SUM({$<YearDate={$(vMaxYearWQ)}>} ClientNumber) => Error: Error in set modifier ad hoc element list: ',' or ')' expected

Thanks in advance for your answers guys

Re: Set Analysis Expression Using Variables

simondachstr — Wed, 11 Feb 2015 14:30:54 GMT

Your best approach would be to keep the expressions in a Excel file, load them as a table into QlikView and then use to peek() function to declare your variables. ' causes an issue when you are using LET.

Re: Set Analysis Expression Using Variables

Anonymous — Wed, 11 Feb 2015 18:49:05 GMT

Hello Cristian,

LET vMaxYearL2='=Max(YearDate)';

LET vMaxMonthL2='=Max(MonthDate)';

Re: Set Analysis Expression Using Variables

omerfaruk — Tue, 03 Mar 2015 15:20:53 GMT

Hello,

In the script side, as Qlikview tries to interpret some of the non-alphanumeric characters like $ ' #, as a function, a workaround would be typing these characters' Ascii code in 'chr(XX)' (where XX Ascii code) format.

So if you try following in your script it will work:

Instead of

LET vIn2010 = SUM({$<YearDate={2010}>} ClientNumber)

// I assumed YearDate is a Date field and removed the single quotes

use this:

LET vIn2010 = 'SUM({' & chr(36) & '<YearDate={2010}>} ClientNumber)';

Ansi Codes of frequently used non-alphanumeric chars:

34 "

35 #

36 $

39 '

42 *

Hope this helps.

Omer.