Variables in load script string formula

[MPkg]

Hello,

I am trying to make  a chart formula inside of the load script as a string. Everything works as it should except one thing.

When I try to implement a variable inside the string formula I get a situation that this variable cannot be changed any more, in other words it becomes a constant with a value equals the value it had in the moment of loading the script.

*For example,

if I try to make this formula in the load script:

load script:

let vChartFormula = 'SUM({"$(vSelect)"}Month)';

let vSelect = 'January';

and when I now use this variable vChartFormula in the chart I only get formula like this:

chart function:
SUM({"January"}Month)

 and anything I change in variable vSelect does not impact the chart function.

I can set vSelect = 'February', nothing changes.

Am I doing something wrong, or is this not possible in Qlik Sense at the moment?

Thanks!

[rwunderlich · Accepted solution]

The $(vSelect) gets substituted in the script. You can avoid this problem by coding your let like:

let vChartFormula = 'SUM({"$' & '(vSelect)"}Month)';

-Rob

[MPkg]

Hello Rob,

thank you very much for your answer.

I have tried adding '&' symbol to the string but the result is unfortunately the same. Nothing changes in the formula if I change vSelect value.  

[rwunderlich]

I would expect it to change. However, 

SUM({"January"}Month)

is not a valid expression so it may not be helpful if it did. 

1. Can you post the contents of vChartFormula from the Variable Editor?

2. What are you trying to calculate with this vChartFormula?

-Rob

[eddie_wagt]

I also wondering what here is being calculated. I find it difficult to make a sum of a month? Anyway, I think the set analysis/expression isn't correct:

Sum ({$<Month="January">} Month)

or

Sum ({$<Month="$(vSelect)">} Month)

And don't forget @rwunderlich advice for loading the variable in the loadscript like he mentioned. If you are looking for other suggestions, then we need more information I guess..

Kind regards

Eddie

If this answers your question or solves your issue, be sure to mark the answer as correct by clicking 'Accept as Solution'. This will mark the post as solved and other Qlikkies will gravitate towards this post as it as a possible solution for their issue. Multiple responses can be accepted as a solution so make sure to select all that apply.

[MPkg]

Hello Rob and Eddie,

The code for expression is correct as {"January"} represents the name of the bookmark that I want to apply to the field. It is true that Sum of Month sounds strange but it should be only a random name of a Field. You can take any data for this field, for example Costs.

I want to make a SUM({"January"}Costs) for Bookmark selection named January.

Thank you both for the fast reply, but I tried with '&' and it did not work.

[eddie_wagt · Accepted solution]

Hello,

Ah now I see what you mean.

Try this

SET vChartFormula_tmp= "SUM({/#(vSelect)/}Month)";
LET vChartFormula = replace(replace(vChartFormula_tmp,'#','$'),'/','"');
LET vChartFormula_tmp = NULL();
let vSelect = 'January';

LOAD * INLINE [Month, Costs
January,100
February,200
];

In Expression

SUM({"$(vSelect)"}Costs)

Regards Eddie

[MPkg]

Hello,

I have accepted both of your answers because they are correct.

I realized  that I have another problem in my formula. So maybe you can help me with this as well.

I am using for loop in load script to make a formula like this:

for i = 1 to 5
let vChartFormula_$(i) = 'SUM({"$(vSelect)"}Cost_$(i))'

if i = 1
   let vChartFormula = '$(vChartFormula_$(i))';
else
   let vChartFormula = '$(vChartFormula)'&'+'&'$(vChartFormula_$(i))';
end if
next i

let vSelect = 'January';

 This is why I cannot change vSelect value in chart expression after entering the formula to the chart.

Do you have some solution to this case, what to change in the load script so I can set vSelect = 'February' in the chart expression?