The Magic of Dollar Expansions

Ask a Question

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 For – Next 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:

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