Qlik Community

Qlik Design Blog

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

Henric_Cronström

A couple of weeks ago I wrote about the Magic of variables and showed how you can use these in advanced expressions. Today’s post will continue on this topic, but now with a focus on the dollar expansions.

First of all, it is important to understand that variables and dollar expansions are – although intimately connected – still not the same things. You can use variables without dollar expansions, and you can use dollar expansions without variables.

 

One common example is Set analysis, where a dollar expansion with an expression is commonly used instead of one with a variable, e.g:

 

Sum( {$<[YearMonth] = {"<=$(=Max([YearMonth]))"}>} Amount )

 

Note that there are no variables used here.

 

Secondly, a dollar expansion is something that is done before the expression (or statement) is evaluated. An example is an include statement in the script:

 

$(Include=some_script_file.qvs)

 

This is a dollar expansion and obviously it is expanded before the resulting script is parsed and executed. Otherwise the Include statement wouldn’t work... Further, in a chart the dollar expansion is made before the cube is expanded, so it cannot expand to different expressions for different dimensional values.

 

Since the expansion is done before the parsing, you can use dollar expansions to create tokens for the expression. In other words – the dollar expansion is not just a value used for the calculation; rather it is something you can use to build your expression.

 

One use-case is if you want to load a large number of similar files: Then you would need a ForNext loop that loops over the files and concatenates them into one table. This is easy if all files have identical set of fields.

 

But what if the files have slightly different sets of fields? Then the auto-concatenate will not work, so you will need to use the Concatenate prefix. But you must not use this on the first iteration, only on the second and subsequent iterations.

 

One solution is to use a dollar expansion:

 

Set vConcatenate = ;

For each vFile in FileList('.\*.txt')

Data:

$(vConcatenate)

LOAD * FROM [$(vFile)];

Set vConcatenate = Concatenate ;

Next vFile

 

Here the variable vConcatenate is initiated to an empty string, so the first time it is expanded in front of the Load, it is expanded to nothing. But in the second iteration it will produce a correct Concatenate prefix, i.e. a script keyword. The result can easily be seen in the script debugger:

 

Concatenate.png

 

You can use dollar expansions to generate tokens also in the user interface. One use-case is that you want to define a set analysis definition in which all selections in an entire dimension are cleared, e.g. you want to clear all fields in the Master Calendar without having to explicitly list them all.

 

One good solution is to define a variable that can be used as Set modifier:

 

Set vClearCalendar = "='[' & Concat({1<$Table={'Calendar'}>} $Field, ']=,[') & ']=' ";

 

and then use this in a chart expression:

 

Sum ({$<$(vClearCalendar)>} Amount)

 

This expression uses the system fields $Table and $Field to find the possible fields of the table “Calendar”. The Concat() function lists these fields with the appropriate delimiters so that the Set modifier contains the relevant list of fields:

 

Sum ({$<[Date]=,[Day]=,[Month]=,[Week]=,[Year]=>} Amount)

 

This effectively removes all selections in the Calendar table.

 

Dollar expansions are extremely powerful in many situations where you want to simplify for the user by hiding the difficult parts in a formula. Use them wisely.

 

Thank you, Oleg for inspiration and examples.

 

HIC

 

Further reading related to this topic:

The Magic of Variables

The Little Equals Sign

29 Comments
Not applicable

great post and also another great example of utilizing the concat() function and the first really useful example of using the $Table and $Field values.  Thanks!

0 Likes
8,680 Views
borisman
Creator III
Creator III

one of my favorite posts of HIC!

BTW looks like this technique does not work in Qlik Sense for some reason - bug? Variable is populated properly but expression always would return null. I use this technique pretty often in QlikView 11

Set vClearCalendar = "='[' & Concat({1<$Table={'Calendar'}>} $Field, ']=,[') & ']=' ";

Sum ({$<$(vClearCalendar)>} Amount)

0 Likes
8,680 Views
Henric_Cronström

Most likely a bug. I cannot verify, since I don't have the 1.0 installed any longer...

However, this is what it looks like in the 1.1 (to be released). On the top you have a chart with the Set Analysis. Bottom left a selection in Month. And bottom right what the evaluate variable looks like in a text box. In other words: It seems to work.

HIC

ClearCalendar.png

8,722 Views
borisman
Creator III
Creator III

thanks Henric, will test it out again once 1.1 is released and patiently waiting for your new blog posts! I now refer all our new QV developers to your blogs as a part of our training program

0 Likes
8,722 Views
qlikviewwizard
Master II
Master II

Hi HIC, Nice information

0 Likes
8,722 Views
Not applicable

One more excellent post

Thank you

0 Likes
8,722 Views
Not applicable

Hi!! What is the function of $::

Thanks!

0 Likes
8,722 Views
Henric_Cronström

The dollar sign is used for several things. In Set Analysis, it means the default selection. The notation $:: means that you get a field selection from the default selection.

An example: If you have an Alternate state called "State2", you may want to write

     {State2<Year = $::Year>}

which means "use the State2 for the calculation, but with the selection in Year from the default selection."

So, this has nothing to do with dollar expansions.

8,722 Views
Not applicable

Thanks !

0 Likes
8,531 Views
Not applicable

Hi Henric

This is very helpful - one question though... you mentioned...

"in a chart the dollar expansion is made before the cube is expanded, so it cannot expand to different expressions for different dimensional values."

Is there any way to get around this limitation? - I'm trying to write a nested expression that calculates significance, but am finding this issue - when I use $(=$(vExpression(par1,par2)) - it gives the same result for all values of the dimension...(and any other combination of $ etc gives errors...)

I'm fairly new to Qlik, and would appreciate any hints here...

thanks, Mike

0 Likes
8,531 Views