Qlik Community

Qlik Design Blog

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

Employee
Employee

The Magic of Dollar Expansions

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

28 Comments
kalyandg
Contributor II

HIC,

Super

0 Likes
77 Views

One more Magic !!! Excellent Post as always...

0 Likes
77 Views

Henric

I have been meaning to dig into the in's & out's of dollar expansions for a few weeks now, so this blog is perfect for me.

Many Thanks,     Bill

0 Likes
77 Views
Not applicable

slick...very cool!

0 Likes
77 Views
kouroshkarimi
Contributor III

That concatenate tip is genius. Thank you Henric.

0 Likes
77 Views
Not applicable

Dollar expansion has been a great tool in my journey of developing Qlikview applications. Can't thumbs up this post enough.

0 Likes
77 Views

Hi HIC,

Nice information

download.jpg

77 Views
krishnacbe
Valued Contributor III

Super!!!

0 Likes
77 Views
walidhussein
Contributor

very helpful article. Thanks Henric.

0 Likes
77 Views
marthacano01
Contributor

Thanks!! very good information

0 Likes
77 Views
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
77 Views
borisman
Contributor 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
77 Views
Employee
Employee

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

77 Views
borisman
Contributor 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
77 Views
Arjunarao
Honored Contributor II

Hi HIC, Nice information

0 Likes
77 Views
Not applicable

One more excellent post

Thank you

0 Likes
77 Views
Not applicable

Hi!! What is the function of $::

Thanks!

0 Likes
77 Views
Employee
Employee

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.

77 Views
Not applicable

Thanks !

0 Likes
77 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
77 Views
Employee
Employee

No, the dollar expansion is always made before the cube is expanded, so this you cannot change. Further, also the expression that defines the measure needs to be fixed before the cube is expanded. The only thing that can vary between the different rows are the values of the fields used in the expression.


You can do the following: Create a dollar expansion that expands to a fix expression and not a value. Then you can use the values of the fields to calculate different things on different rows.

I don't know what your vExpression contains, so it's impossible for me to know, but it could be that the following does the trick:

=$(vExpression(par1,par2))

HIC

0 Likes
77 Views
Not applicable

Thanks Henric,

I thought that would be the case - I've ended up combining the nested expression into the "parent" expression and it works ok now - not sure what the performance implications would be though...

Thanks for your response (and articles all very helpful)

Mike

0 Likes
77 Views
denis585
New Contributor II

Hello, Henric

Thank You for the article.

I'm trying to implement your logic of concatenate example for Hierarchy prefix

QlikSense marks those as an error.

$(vTabQlik):

NoConcatenate

$(vHierarchyExpression)

LOAD $(vFieldList) ;

SQL SELECT *

FROM UPP.dbo.$(vTabERP)

Where $(vWhereExpression);

Image02.PNG

But the script works fine instead of marking.

0 Likes
77 Views
Employee
Employee

If the script works, then you have done everything correct.

However, the syntax checker in the script editor gets lost very easily when you use dollar expansions. In principle, it is not possible for the syntax checker to "know" what's in a dollar expansion. And then you get "errors" that really aren't errors.

HIC

0 Likes
77 Views
denis585
New Contributor II

Henric, Thank You for your explication.

0 Likes
77 Views
mountaindude
Contributor III

If the script works, then all is fine.

But considering how you used the variables, the script would break if for example vTabQlik='My Table', whereas the script would work just fine if that same variable equals 'MyTable'. Note the space in the first example that would break the script. While using variables this way is quite powerful, you have to keep tight control of what goes into them..

0 Likes
77 Views
beck110979
Valued Contributor III

Thanks a lot for sharing

0 Likes
77 Views
mmarchese
New Contributor III

How do we use that spiffy calendar-filter-clearing technique in Qlik Sense?  For instance, one of my date fields is effectiveDate.  The auto-generated fields are thus effectiveDate.autoCalendar.Date, effectiveDate.autoCalendar.Year, etc.  I tried this and a few other variations without any success:

The last line in my data load editor script:

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

The expression in a KPI object:

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

I know I'm missing something, but I'm not sure what.  Note that the expression below works fine, but would be very cumbersome to implement for every possible date field (I only did 3 of them):

Sum({<[effectiveDate]=, [effectiveDate.autoCalendar.Date]=,  [effectiveDate.autoCalendar.Month]=>}Weight)

0 Likes
77 Views