Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis Expression Using Variables

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

8 Replies
NickHoff
Specialist
Specialist

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

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

Not applicable
Author

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
Creator II
Creator II

Hi Cristian,

Can you remove the quation marks

SET vMaxYear=Max(YearFromDate);

SET vMaxYear=Max(YearFromDate);

see if it works

Peter_Cammaert
Partner - Champion III
Partner - Champion III

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

Not applicable
Author

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
Luminary Alumni
Luminary Alumni

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
Not applicable
Author

Hello Cristian,

LET vMaxYearL2='=Max(YearDate)';

LET vMaxMonthL2='=Max(MonthDate)';

omerfaruk
Creator
Creator

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.