topic Re: Variables loaded from Excel file in App Development

Variables loaded from Excel file

gfaccenda — Fri, 10 Jan 2025 15:16:49 GMT

Hi, I'm trying to load 2 variables from an Excel file, and use the first one in the set analysis of the second one.
Here are the variables:

vDateRange	if(vSwitch=1, '>='&monthstart(addmonths(min(YearMonth),-2))&'<='& monthend(addmonths(max(YearMonth),-1)), '>='&monthstart(addyears(min(YearMonth),-2))&'<='& monthend(addyears(max(YearMonth),-1)))
vExpression	sum({<[Date]={"$(=vDateRange)"}>}[Amount])

I tried every possible combination of dollar and equal signs in both variables but i can't get it to work.

In the app, when using "vExpression" in a measure i get 0 as result. The preview of the formula shows me:

sum({<[Date]={"if(vSwitch=1,
'>='&monthstart(addmonths(min(YearMonth),-2))&'<='& monthend(addmonths(max(YearMonth),+1)),
'>='&monthstart(addyears(min(YearMonth),-2))&'<='& monthend(addyears(max(YearMonth),+1)))"}>}[Amount])

sum({<[Date]={}>}[Amount])

depending on the combination of $ and = used.

When I tried to load the same vExpression from the variable panel it's working properly, but not when loaded from the Excel file.

I want the vDateRange variable in the set analysis to be evaluated, but it has to be evaluated at a chart level, not when the app is being loaded.

e.g sum({<[Date]={">=01/11/2024<=31/12/2024"}>}[Amount])

Thanks a lot for your help

Re: Variables loaded from Excel file

rwunderlich — Fri, 10 Jan 2025 17:38:11 GMT

Can you post the script you are using to load the variables from Excel?

-Rob

Re: Variables loaded from Excel file

gfaccenda — Mon, 13 Jan 2025 08:46:50 GMT

Hi Rob,
The script is the following:

Variables: LOAD Name, Value FROM [lib://PATH/Variables.xlsx] (ooxml, embedded labels, table is Sheet1); FOR var_i = 1 TO NoOfRows('Variables') l_var = PEEK('Name', var_i - 1, 'Variables'); l_val = PEEK('Value', var_i - 1, 'Variables'); SET $(l_var) = $(l_val); NEXT var_i; Let var_i =; Let l_val =; Let l_var =;

Re: Variables loaded from Excel file

marcus_sommer — Mon, 13 Jan 2025 15:45:35 GMT

You may try:

vDateRange	if(vSwitch=1, '>=monthstart(addmonths(min(YearMonth),-2))<= monthend(addmonths(max(YearMonth),-1))', '>=monthstart(addyears(min(YearMonth),-2))<= monthend(addyears(max(YearMonth),-1))')
vExpression	sum({<[Date]={"$(=$(vDateRange))"}>}[Amount])

Re: Variables loaded from Excel file

rwunderlich — Mon, 13 Jan 2025 16:32:32 GMT

You have an error in your script. You should be PEEKing the variable value directly to avoid the Dollar Sign Expansion (DSE). Like this:

FOR var_i = 1 TO NoOfRows('Variables') l_var = PEEK('Name', var_i - 1, 'Variables'); Let $(l_var) = PEEK('Value', var_i - 1, 'Variables'); NEXT var_i;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com