topic Re: Set Analysis - and use of Variables in App Development

Set Analysis - and use of Variables

andrew_smith200 — Fri, 02 Apr 2021 08:10:29 GMT

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

Re: Set Analysis - and use of Variables

Anil_Babu_Samineni — Wed, 06 Nov 2019 11:39:51 GMT

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)';

Re: Set Analysis - and use of Variables

andrew_smith200 — Wed, 06 Nov 2019 12:15:48 GMT

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.

Re: Set Analysis - and use of Variables

andrew_smith200 — Wed, 06 Nov 2019 14:05:15 GMT

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.

Re: Set Analysis - and use of Variables

dwforest — Wed, 06 Nov 2019 20:48:39 GMT

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)

Re: Set Analysis - and use of Variables

andrew_smith200 — Thu, 07 Nov 2019 08:51:11 GMT

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;

Re: Set Analysis - and use of Variables

Anil_Babu_Samineni — Fri, 08 Nov 2019 07:13:42 GMT

@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.

Re: Set Analysis - and use of Variables

marcus_sommer — Fri, 08 Nov 2019 14:57:32 GMT

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