Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

How to consolidate variables (expressions) with set_expressions

Hi,

there is one chart - the presence_quota - that is present in all my apps, but for a few.

The expressions in this chart - and in the chart "historical values" that is always next to it - should always be calculated in the same way of course.

The issue is, they contain set_expressions which are not executable in the script, so I cannot put them in a qvs file.

=> Is there any other way I could store these centrally so as not to have any potential for redundancy and typos?

Thanks a lot!

Best regards,

DataNibbler

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Yes, that's correct. The set expressions won't work in the script, but they're not used there either. You only define the variables containing the expressions in the script. You use the variables in chart expressions so that when the variable is dollar expanded the expression you defined in it is evaluated in the context of the chart.


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
Gysbert_Wassenaar

You can define variables in a shared script file that contain the expressions.

SET vMyExpression = sum({<MyField={'Foo'}>} MyAmount);

If you need to use variable expansions inside your expression you'll have to use some tricks to prevent the dollar expansion happening in script:


LET vMyExpression = 'sum({<MyField={$' & '(vMyVariable)}>} MyAmount)';


talk is cheap, supply exceeds demand
datanibbler
Champion
Champion
Author

Hi Gysbert,

that would be (in the script_editor) via >> main_menu "include" -> script <<, right?

<=> But in the script, set_expressions don't work, do they?

tresesco
MVP
MVP

You are not going to use these variables (which contain the expression as a text) in the script, rather they are just being defined to hold a text. You have to use them in the front end with the trick as mentioned above by Gysbert Wassenaar

Gysbert_Wassenaar

Yes, that's correct. The set expressions won't work in the script, but they're not used there either. You only define the variables containing the expressions in the script. You use the variables in chart expressions so that when the variable is dollar expanded the expression you defined in it is evaluated in the context of the chart.


talk is cheap, supply exceeds demand
datanibbler
Champion
Champion
Author


Hy Gysbert,

okay, now I have it. The script treats all that as regular strings and it is used in the chart only.

That seems a good thing to have. Everything I can do to recuce the potential for error and incongruencies should be done.

One more question: From that pattern (with the quotes), I cannot quite imagine how I would do it if my set_expressions contains not one, but several fields.

datanibbler
Champion
Champion
Author

Hi,

the principle is working fine - I can define that expression in the script of one of my apps, so I should be able to also do it in a shared script - or in a qvs file, that should not make a difference given the variable-expansion is not done in the script, no?`

<=> The figures, however, are incorrect. I will just paste it here, maybe you can think of a reason:

>> LET v_Ges_day = 'sum({$<%Datum = {"$' & '(= chr(60) & DATE(v_today))"}, Werktag_v2 = {1}>}Anz_MA_soll)'; <<

=> That is,

- I sum up a binary field (1 or 0),

- but only for all the days (inside the week selected by the user, by default the current one) up to "yesterday"
   (chr(60) is a '<') - I had to introduce that to avoid QlikView becoming confused with the quotes and

- only for working_days (Mon to Fri, plus there should be no display on a public holiday)

=> I don't know why, but that expression, when I select one specific day, returns a figure four times as high as the regular expression.

Thanks a lot for every help you can offer me!

Best regards,

DataNibbler

P.S.: When I take out that set_expression (thus only keeping the sum(), the figures are correct, so the error probably lies there - but all the variables and fields I use in there come from a master_calendar (qvs) which is loaded previously?

datanibbler
Champion
Champion
Author

Done.

The formula was ignoring my selection of the date - the dimension of the chart is the date - four days are available for this week, each one with a sum of 110, the formula said 440.

I thus introduced another date_field in that table which is specific to it, no key_field and selected on that - that worked.

Then I tried using that formula in the chart (with the name of that new date_field in the formula) -> voila!

datanibbler
Champion
Champion
Author

Hi Gysbert,

are you still around?

I have made it in principle, I have incorporated eight expressions - two actual figures and two quotas, each for the two dimensions - into a qvs file and I'm now just going to load that into every app that has this chart and all should be fine.

Another chart, the "historical values" one, however, is still more complex. Can you help me a bit with that, please, can I post or attach it here? It's basically one of the quotas, but fixed on the previous week and previous month respectively.

Thanks a lot!

Best regards,

DataNibbler