Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Discussion board where members can learn more about Qlik Sense App Development and Usage.

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Variables in load script string formula

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

MPkg

Contributor III

2022-06-14
05:27 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Variables in load script string formula

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!

236 Views

2 Solutions

Accepted Solutions

rwunderlich

MVP

2022-06-15
12:09 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

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

-Rob

eddie_wagt

Partner - Creator III

2022-06-16
08:02 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

153 Views

7 Replies

rwunderlich

MVP

2022-06-15
12:09 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

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

-Rob

MPkg

Contributor III

2022-06-15
07:27 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

192 Views

rwunderlich

MVP

2022-06-15
10:25 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

184 Views

eddie_wagt

Partner - Creator III

2022-06-15
11:09 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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. |

179 Views

MPkg

Contributor III

2022-06-16
06:53 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

Partner - Creator III

2022-06-16
08:02 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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

154 Views

MPkg

Contributor III

2022-06-17
03:49 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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?

119 Views