Set Analysis Expression Using Variables

[#1]

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

[NickHoff]

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

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

[#3]

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

[saurabh5]

Hi Cristian,

Can you remove the quation marks

SET vMaxYear=Max(YearFromDate);

SET vMaxYear=Max(YearFromDate);

see if it works

[Peter_Cammaert]

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

[#6]

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

[simondachstr]

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.

[Anonymous]

Hello Cristian,

LET vMaxYearL2='=Max(YearDate)';

LET vMaxMonthL2='=Max(MonthDate)';

[omerfaruk]

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.