Read in variables for expressions in Qlik Sense

[catrauth]

I have created a Qlik Sense app to read in variables to use in the expressions for date ranges.

After reading in the variables from a spreadsheet and applying them to the relevant expression they are not reducing the data.

The same expressions built into the app appear to work correctly.

Any idea what is going wrong?

The app and the data are attached.

[Digvijay_Singh]

Looks like your script is not working, I don't see '~' is replaced by '$'. See the image of variables, also why are you loading variable xlsx twice? -

Variables:

LOAD

    Name,

    "Definition",

    "Replace"

FROM [lib://Data Files/All dates data.xlsx]

(ooxml, embedded labels, table is [Master Variables]);

//Why 2nd time load below?

LOAD

    Name,

    "Definition",

    "Replace"

FROM [lib://Data Files/All dates data.xlsx]

(ooxml, embedded labels, table is [Master Variables]);

[zebhashmi]

is that..

[catrauth]

Mistake on my part there should not be two load statements.

That is not the problem though.

'~' is replaced by '$' is  in the  For loop just before the else statement.

[catrauth]

As pointed out previously a mistake on my part but does not resolve the original problem.

Once read in the variable do not reduce the data correctly in the charts.

[Digvijay_Singh]

This bold line change start replacing '~' to '$' as needed -

FOR i = 0 TO NoOfRows('Variables') -1

  LET vVarName = Peek('Name', $(i), 'Variables');

  if match(Peek('Replace', $(i), 'Variables'), 'Yes') then

   LET vTemp = Peek('Definition', $(i), 'Variables');

   SET $(vVarName)_temp = '$(vTemp)';

   LET $(vVarName) = replace($(vVarName)_temp, '~', '$');

  else

SET $(vVarName) = $(vTemp);

  end if

NEXT

[catrauth]

Changing to LET does not resolve the problem.

On the Call sheet try start date of 8/1/2015 and end date if 10/31/2015.

You will see the chart on the right returns two records.

If you substitute the Variable tab in the script for the Start and End Calendars and reload you will see the correct answer is returned.

[catrauth · Accepted solution]

The problem turns out to be in the Master Variables spreadsheet.

The vStartDate and vEndDate both require an = sign at the beginning.

In Excel you need to enter the = sign when you enter the expression otherwise it will try to evaluate the expression and Excel wil not lie it and give you an error.