Dollar-sign Expansion is a process that allows us to replace text in an expression, or line of script, with either the value of a variable or some other calculation.
Suppose that we have a variable with a value of 10 and we write an expression like the following:
Sum(If(Field1=$(vValue), 1, 0))
The Dollar-sign expression, $(vValue), will get expanded to its value (10) and the expression that gets executed will be as follows:
Sum(If(Field1=10, 1, 0))
We can also have a calculation inside the Dollar-sign expression like the following:
If(Year=$(=Year(Today())), LightGreen())
In this case, the function Year(Today()) will be calculated and its value replaced into the main expression in the following manner:
If(Year=2014, LightGreen())
We do have to be aware that it is the exact value of the Dollar-sign expression that is replaced into the main expression, and it becomes as if we have typed that value there. Therefore, if it is a string value rather than a numeric value, then we need to make sure that we include the single quotes around the value.
For example, suppose that we have a variable called vCountry with a value of Germany and we have a color expression in a bar chart like the following:
If(Country=$(vCountry), LightGreen())
We might be surprised to find that the Germany bar is not highlighted. This is not such a surprise if we consider that the Dollar-sign Expansion will result in the following expression:
If(Country=Germany, LightGreen())
To QlikView, this looks like you are trying to compare the Country field to another field called Germany. Instead, we should have our original expression as shown:
If(Country='$(vCountry)', LightGreen())
This will expand out to the following:
If(Country='Germany', LightGreen())
This issue is equally critical with dates. The problem is that sometimes all looks OK, but we need to consider that, without quotes, the value 8/9/2014 will actually be evaluated to 4.413549597263599e-4 (8 divided by 9 and then divided by 2014).
With dates, you can use quotes and then they will be evaluated correctly—as long as the text of the date matches the field's date format. However, it can often be a better practice to use a function like Floor or Num to transform your dates into numbers instead of relying on the text format being correct.
As was mentioned in the Reviewing basic concepts section, whenever Dollar-expansion is used, there is always a two-step process followed:
- The expression or variable inside the Dollar-sign Expansion's parentheses is calculated and its value is placed into the expression, or script line, to replace the dollar-sign.
- The newly formed expression or script line is executed.
We can use the script debugger to follow the two steps of the Dollar-sign Expansion process. For example, suppose that we had a piece of script as shown:
Let vMinDate=Floor(MakeDate(2009,1,1)); Let vMaxDate=Floor(MakeDate(2014,12,31)); Let vDiff=vMaxDate-vMinDate+1; DI: Load TempDate as DateID, Year(TempDate) As Year, Month(TempDate) As Month; Load $(vMinDate)+RecNo()-1 As TempDate AutoGenerate($(vDiff));
If we run the script debugger and put a breakpoint on the second load statement, we can observe what is happening when the Dollar-sign Expansion happens:
If we look at the central panel, we can see that the Dollar-sign Expansion has replaced $(vMinDate) with 39814 and $(vDiff) with 2191. The original expression is as follows:
Load $(vMinDate)+RecNo()-1 As TempDate AutoGenerate($(vDiff));
This preceding code is now changed to the following:
Load 39814+RecNo()-1 As TempDate AutoGenerate(2191);
This debugger process is an excellent way of testing our Dollar-sign Expansion in the script.
The best way to follow the steps in a chart expression, and hence to debug the Dollar-sign Expansion, is to use a Straight Table. One of the features of all charts is that if you don't specify a label for an expression, then the expression itself is used as the label, but not just the expression as entered—it is the expression after the first step with the Dollar-sign Expansion complete. This label is the easiest seen in a Straight Table.
For example, I add a Straight Table to my document, with no dimension, and have set the expression to the following:
Sum(If(Country='$(vCountry)', 1, 0))
Then, I can see the expanded expression by hovering over the label of the expression:
One thing that might become apparent here is that because the first step, the actual expansion, happens before the expression is calculated; this means that the first step is not calculated in reference to the dimensions of the chart. The first step is calculated outside of the chart.
This can be a slight downside because it means that we can't successfully use a chart's dimension value in a Dollar-sign Expansion expression used inside that chart.
Just to illustrate this, suppose that I add a Straight Table with Country as a dimension, and then, I add the following expression:
'$(=MaxString(Country))'
I might expect that this should just calculate out the same value as the dimension on each row. However, this is not what happens:
Because the Dollar-sign Expansion has been calculated outside of the chart, it will just calculate MaxString based on the current selections in the document.
We can use parameters in variables and then pass those parameters when we use the Dollar-sign Expansion. This creates a type of macro that can be used in script or in expressions.
A variable parameter is identified with a Dollar-sign and a number. The first parameter will be $1, the second will be $2, and so on.
For example, if I have fields that contain a code and a description separated by a period, I can define a couple of variables in the script in the following manner:
// Macros SET mLefty=Left($1, index($1, '.')-1); SET mRighty=Mid($1, index($1, '.')+1);
Then, I can load my data in the following manner:
Test: Load $(mLefty(Field1)) as Field1.Code, $(mRighty(Field1)) as Field1.Desc Inline [ Field1 001.Value one 002.Value two 003.Value three ];
I can also call it in an expression, for example:
=$(mLefty('004.Field Four'))Of course, this might not work so well in a chart, because of the step order, but it would work well in a text object, caption, and so on.
In many cases, we can just use a variable in an expression as we would in any other programming language:
If(Country=vCountry, Sum(LineValue), 0)
However, if the variable does not contain a simple value, but instead contains an expression, then it will not work like this and we need to use Dollar-sign Expansion instead.
For example, if we have a variable called cCompanyWarning that has a value of ARGB(200,255,126,0), then we cannot simply use this in a color expression because, as far as QlikView is concerned, this is not a color, it is just text. However, suppose that we put it into an expression like the following:
=$(cCompanyWarning)
We can see that it is no longer just text. The text will get replaced into the expression and then QlikView will evaluate the ARGB function as if we had typed it there in the first place.