Variables loaded from Excel file

[gfaccenda]

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])

or

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

[rwunderlich]

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

-Rob

[gfaccenda]

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 =;

[marcus_sommer]

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])

[rwunderlich · Accepted solution]

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