topic Re: Variables in load script string formula in App Development

Variables in load script string formula

MPkg — Tue, 14 Jun 2022 09:27:41 GMT

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!

Re: Variables in load script string formula

rwunderlich — Wed, 15 Jun 2022 04:09:39 GMT

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

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

-Rob

Re: Variables in load script string formula

MPkg — Wed, 15 Jun 2022 11:27:10 GMT

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.

Re: Variables in load script string formula

rwunderlich — Wed, 15 Jun 2022 14:25:05 GMT

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

Re: Variables in load script string formula

eddie_wagt — Wed, 15 Jun 2022 15:09:19 GMT

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)

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.

Re: Variables in load script string formula

MPkg — Thu, 16 Jun 2022 10:53:43 GMT

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.

Re: Variables in load script string formula

eddie_wagt — Thu, 16 Jun 2022 12:02:39 GMT

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

Re: Variables in load script string formula

MPkg — Fri, 17 Jun 2022 07:50:23 GMT

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?