Set Analysis - and use of Variables

[andrew_smith200]

I have the following SET anaysis formula which works fine:

sum(  {$<type_of_info={EPR}, Year = {$(=max(Year))}>}               ReportCounter)

However when I try to put this into a variable the Year part does not work

LET vReports = 'sum(  {$<type_of_info={EPR}, Year = {$(=max(Year))}>}               ReportCounter)';

Year is defined as a field of my calendar dimension

[Anil_Babu_Samineni]

Are you not getting any data using that variable or It is getting wrong?

Check same variable creating in Variable Overview.. Or try this?

LET vReports = '=sum({$<type_of_info={'EPR'}, Year = {"$(=max(Year))"}>}  ReportCounter)';

[andrew_smith200]

Thanks but the max year section will not resolve it just leaves the quotes with no value displayed. If you don't put it into a variable it works fine.

[andrew_smith200]

so if you put max(year) into a variable and substitute it in the string it then works when you create the overall variable. Just interesting that it works when not in a variable.

[dwforest]

you have to build the string so the $(=max(Year)) can evaluate

LET vReports = 'sum(  {$<type_of_info={EPR}, Year = {' + $(=max(Year)) + '}>}               ReportCounter)';

Use Test & image to display the value of your variable, the string should contain all evaluated values

=Sum(  {$<type_of_info={EPR}, Year = {2019}>} ReportCounter)

[andrew_smith200]

Sorry but this doesn't work - I have never seen '+' used in this way. If you remove them it still doesn't work as it doesn't resolve with single quotes. The only way is to create a variable in the load script calling maxyear and then inserting into the variable statement something like this:

tmp:

load max(Year(DateID)as MaxYear resident MasterCalendar;

LET vMaxYear = floor(peek('MaxYear'));

LET vAllreports = 'sum( {$<type_of_info={EPR}, Year = {$(vMaxYear}}>}  ReportCounter)';

drop table tmp;

[Anil_Babu_Samineni]

@andrew_smith200 

Seems there are many Single and double quotes. Qlik doesn't know few of the parts. We need to write as per Qlik. Can you try this way?

LET vReports = '=sum({$<type_of_info={'''EPR'''}, Year = {''"$(=max(Year))"}>}  ReportCounter)'; // Red color part single quote not the double quote.

[marcus_sommer]

The essential point is that Qlik evaluates always the variable at once - and $(=max(Year)) isn't valid in the script. This meant you couldn't simply create UI expressions as variables within script if they contain such variables.

I'm not very familiar with Sense but in View there would be the suggestion to create such expression-variables within the variable-editor - AFAIK Sense hasn't this native feature but I believe some extensions are available for this.

If not or if it's not practicable enough you could as a workaround replace the $-sign within your variable-content with any unique char (in regard to the usual alphanumeric chars) - maybe chr(1) - and then afterwards replacing it again with the $-sign, like:

let a = 'sum({< F = {"' & chr(1) & '(=max(F))"}> X)';
let b = replace('$(a)', chr(1), '$');

Alternatively you could load your expressions from a table and assigning then the variables within a loop with their content, like: loading-variables-via-loadscript .

- Marcus