Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

The Magic of Variables

Variables can be used in many ways in QlikView. They can have static values or they can be calculated. But when are they calculated?  At script run-time or when the user clicks? And how should they be called? With or without dollar expansion?

 

One basic way to assign a value to a variable is to use a Let statement in the script:

 

     Let vToday  = Num(Today()) ;

 

This will calculate the expression and assign it to the variable when the script is run. This is exactly what you want if you want to use a variable as a numeric parameter in your expressions.

DocProp.png

 

But if you want the expression to be evaluated at a later stage, e.g. every time the user clicks, what should you do then? One way is to store the expression as a string in the variable, using either the Set or the Let statement or by defining it in the Document Properties -> Variables:

 

     Set vSales  = Sum(Sales) ;

     Let vSales  = 'Sum(Sales)' ;

 

In neither case, the expression will be calculated. The variable will contain the string ‘Sum(Sales)’, which subsequently can be used in an expression using a dollar expansion: $(vSales).

 

With a dollar expansion, QlikView will substitute the ‘$(vSales)’ with ‘Sum(Sales)’ before the expression is evaluated. Some of you will recognize this as an old style assembler macro expansion. The subsequent calculation will be made based on the evaluation of the resulting expression. Note the two steps: (1) Variable expansion; and (2) Expression evaluation.

 

Chart.png

 

In the chart above, you can see the result of using a normal variable reference (the first expression) or using a dollar expansion (the second expression). In the second expression, the variable is expanded and the numbers are calculated correctly.

 

But this is just the beginning…

 

It is also possible to calculate the variable value, i.e. determine how it should be expanded, by using an initial equals sign in the variable definition.

 

     Let vSales2  = '=Sum(Sales)';

 

In this case, the variable value is calculated after each click, whereupon the dollar expansion in the chart expression is made, and finally the expression is evaluated. This means that the evaluation of ‘Sum(Sales)’ is done before the variable expansion. Note the three steps: (1) Variable calculation; (2) Variable expansion; and (3) Expression evaluation.

 

The table below summarizes the three methods.

 

What you see 2.png

 

With the above, you can do almost magical things. You can for instance make conditional calculations that depend on e.g. selections, client platform or user.

 

Example:

  • Create a field [Field to Aggregate] containing the names of two other numeric fields: 'Quantity' and 'Sales'
  • Create a variable vConditionalAggregationField = '=Only([Field to Aggregate])'
  • Create a chart with an expression = Sum($(vConditionalAggregationField))

 

The calculation in a chart will now toggle between Sum(Quantity) and Sum(Sales) depending on your selection.

 

Quantity.png   Sales.png

 

The use of variables is an extremely powerful tool that you can use to create flexible applications. Use it – but with caution. Too much magic behind the curtains can be confusing.

 

HIC

 

Further reading related to this topic:

The Magic of Dollar Expansions

The Little Equals Sign

44 Comments
rustyfishbones
Honored Contributor II

Thanks Henric,

I have seen some apps where it looks like a scene from the movie Inception!

0 Likes
3,153 Views
carbal1952
Contributor II

Thanks Henric: As usual: so clear, so accurate, so essential.

CB.

3,153 Views
b_garside
Valued Contributor

Great post. Really clarifies a topic, not so well covered in any books or post until now.

I see a lot of potential with this last piece.

Thanks Henric

0 Likes
3,153 Views
jeremy_fourman
Contributor

Very good posting, thank you!

3,153 Views
Not applicable

I'm printing this out and posting on my wall. Great reference!

3,153 Views
kalyandg
Contributor II

hi HIC,

nice blog

0 Likes
3,153 Views
MVP
MVP

Very straight forward and useful

0 Likes
3,153 Views
Not applicable

Thank you Henrich for this good overview. I'm in the process of implementing some thing I have learned at the master summit for qlikview and one of them was about variables. I need a set analysis wherein a filter is set for every colomn of a certain table. This is the definition of the variable in the script:

SET filter='{<['&concat({<[$Table]={"TABLE_NAME"}>}[$Field],']=, [')&']>}';

when I use this in a text box:

sum($(filter) column)

It works with no problems. But when I use the same expression in a straight table I get a 0.

When I change the expression in the straight table to:

sum($('filter') column)

Then i get the correct behavior. How do I need to interpret this behavior in relation to your article?

Thanks

0 Likes
3,153 Views

Not sure I understand this. Can you send or post an example?

HIC

0 Likes
3,153 Views
philip_doyne
Contributor

Dear Henric,

I find many occasions when customers are confused as to when they can use the variable name on its own and when they need to use the $() around the variable.  I know this forces the expansion but it does seem to me that sometimes the variable works as you want without the $().   I also find that sometime you have to put '$(variable)' in '' and sometimes not to get the desired result.    Do you think you could come up with a rule or rules(s) for when to use $() or '' or is my current advice "Always put all variables used in Script or Expressions in $() the simplest (must be) and also best option?

Thanks  Philip

0 Likes
3,153 Views
Not applicable

There are a number of other blogs that provide additional examples/explanations. Here are a couple that might help.

http://hyunkucho.wordpress.com/2012/04/14/qlikview-dollar-sign-expansions/

http://www.qlikfix.com/2011/06/08/not-all-variables-are-created-equal/

http://www.naturalsynergies.com/q-tip-6-those-tricky-sign-expansions/

3,153 Views

Philip Doyne

I would lie if I said  that I completely understand how to call variables... Dollar expansion or not? Quotes or not? Equal sign or not? Further, the logic is slightly different in different object types and in different places in an object (label or expression).

Personally, I usually use the dollar expansion and add quotes when needed. I can only think of one situation where a dollar expansion is worse than without: If the variable is numeric and your environment settings use decimal comma. Then further calculations may not work properly.

The only simple advice I can give, is to test your expression properly before you ship the app...

HIC

3,153 Views
philip_doyne
Contributor

Dear Henric,

I feel much happier you don’t fully understand it either! Thank you for your reassurance – I will continue with the use the $ always unless you have a very clear reason not to!

Philip

Philip Doyne

Mobile: 07930 401440

Office : 01709 300230

Email : philip.doyne@qlickit.co.uk<mailtoSmiley Tonguehilip.doyne@qlickit.co.uk>

Web : www.qlickit.co.uk<http://www.qlickit.co.uk/>

3,153 Views
MVP & Luminary
MVP & Luminary

Hi Mathias,

I find that if you want to include the "{<>}" in the variable, the syntax can get a bit tricky. It works best if you include the leading "=" in the variable def to get it evaluated into a SA string before substitution in the chart expression. It can be challenging to get the leading "=" in the variable def, so I switch to LET -- and escape/replace the single quotes with "|". This works for me, to clear all fields from the table "Orders".

LET filter=replace(

'=|{<[| & concat({<[$Table]={"Orders"}>}[$Field],|]=,[|) & |]=>}|'

,'|', chr(39));

And then the expresion usage is:

sum($(filter) column)


-Rob

3,153 Views

Rob - That's a great tip on using replace to convert the pipes "|" to chr(39).

Trying to get this sort of expression by adding single quotes using  &chr(39)& within the string soon becomes unreadable.

0 Likes
3,153 Views
Not applicable

Henric, I was wrong in my previous post, the solution I proposed appeared to work but actually didn't (it just didn't give me an error and i was already happy with that). Rob's solution however works perfectly! Thanks again Rob!

I should have known I had to look a little further in my master summit notes in order to find the solution

Don't ask me why only Robs solutions works and mine doesn't. I think variables are going to stay a mystery to me only implementable by trial and error.

Anyway, here is an example document:

http://community.qlik.com/docs/DOC-5259

3,153 Views

Mathias

As I see it, it is not the variables that are confusing here. Rather, it is the fact that you want to use single quotes on different levels. QlikView needs better escape sequences... . But Rob's method is an excellent work-around that I strongly recommend that you use. It simplifies everything and gives the developer the overview he needs.

HIC

3,153 Views
Not applicable

So if we dispose of better escape sequences, then we can finally escape the confusion! thx

0 Likes
3,153 Views
Not applicable

Dear Henric Cronström,

A great article and easy to understand article! Thank you!

-Mithun

0 Likes
3,153 Views
danieloberbilli
Valued Contributor II

Dear Henric,

A question to your table that summarizes the methods: why do you consider Alt3 =Sum(Sales) as being 'Good for dynamic expressions'? I thought that this method rather gives you a static value than Alt2 Sum(Sales) which also considers the chart dimension...so would Alt2 not be more dynamic than Alt3?

Thanks a lot and I really appreciate all your blog posts!

Daniel

3,153 Views

It is probably because the example is bad...

First of all, if you want Alt3 to respect the chart dimension, all you need to do is to define the variable as

     ='Sum(Sales)'

i.e. with quotes instead of without.

With "dynamic" I mean that the expression is not fixed - it can be changed by different conditions before it is evaluated. With the risk of creating an even worse example I suggest you look at the following variable definition:

=If(WeekDay(Today())='Mon','Avg','Sum') & '(Amount)'

This will on Mondays evaluate to 'Avg(Amount)' and on other week days to 'Sum(Amount)'. So if you expand this variable as expression in a chart, you will have different numbers different days of the week. I don't know why you would want this, but is serves as an example that you can calculate the expression, before the expression itself is calculated.

Makes sense?

HIC

3,153 Views
danieloberbilli
Valued Contributor II

Right - I see - thanks a lot for your reply.

All the best from Leipzig

Daniel

0 Likes
3,153 Views
Not applicable

can you add the QVW and data files for this ? Thank you.

EDIT -

I tried to make a qvw file which demonstrates your lesson. Can you please help me to complete the sample app ? It is at - Prefixing a variable with an equals sign

0 Likes
3,153 Views
Not applicable

Hi Rob,

Tip to replace pipe character with chr(39)  when set analysis is included in variable, saved my day.

I owe you..

Thanks @Rob

-Regards

Ramesh

0 Likes
3,153 Views
Not applicable

In Henric's final example, how would one go about making the custom "field to aggregate" box?

Would you create a new List Box and populate it using an expression? If so, what syntax would be used? Or would another method be better?

Example:

  • Create a field [Field to Aggregate] containing the names of two other numeric fields: 'Quantity' and 'Sales'
0 Likes
3,153 Views
MVP & Luminary
MVP & Luminary

alyshakub In this example, one would normally load the field as an INLINE table in the scipt.

LOAD * INLINE [

Field to Aggregate

Quantity

Sales

]

;


-Rob

3,153 Views
Not applicable

Thank you Rob. That worked perfectly.

0 Likes
3,153 Views
Not applicable

Hi Henric,

Thanks for your post.

I have a scenario where I need to sum a number of dynamic columns based on user input from field selection, not sure this can be done.

In the script, I defined a variable to form the full expression:

Set vSumFieldSelections = 'Sum([' & GetFieldSelections([fieldName],']+[') & '])';

Then my expression in one of the QlikView text object:

=$(vSumFieldSelections)

The above code doesn't work, appreciate if you can provide some idea for me to move forward.

Thanks & Regards,

K

0 Likes
3,153 Views

You want to make a two-step evaluation:

  1. Evaluate he GetFieldSelections call
  2. Evaluate the Sum()

but the way you have written it you will just get one step.

However, if you change your Set statement to

   Set vSumFieldSelections = ='Sum([' & GetFieldSelections([fieldName],']+[') & '])';

you will get a variable that is recalculated every time. Then it could get calculated to

     Sum([2015-03-10]+[2015-03-11])

if the field values are dates, and I have selected these two dates.

But this still doesn't help you, since the dates are field values. and you need field names in the sum function. Further, if you want to sum the field values, you just need to use

     Sum( fieldName )

HIC

0 Likes
3,153 Views
Not applicable

Hi Henric, thanks for your help. Finally I make it work by putting only single field in the sum function.

0 Likes
3,153 Views