Set modifiers with dollar-sign expansions
Dollar-sign expansions are constructs that are calculated before the expression is parsed and evaluated. The result is then injected into the expression instead of the $(…). The calculation of the expression is then made using the result of the dollar expansion.
The expression editor shows a dollar expansion preview so that you can verify what your dollar-sign expansion evaluates to.
Use dollar-sign expansions when you want to use a calculation inside your element set.
For example, if you want to look at the last possible year only, you can use the following construction:
<Year = {$(=Max(Year))}>
Max(Year) is calculated first, and the result would be injected in the expression instead of the $(…).
The result after the dollar expansion will be an expression such as the following:
<Year = {2021}>
The expression inside the dollar expansion is calculated based on the current selection. This means that if you have a selection in another field, the result of the expression will be affected.
If you want the calculation to be independent of the selection, use set analysis inside the dollar expansion. For example:
<Year = {$(=Max({1} Year))}>
Strings
When you want the dollar expansion to result in a string, normal quoting rules apply. For example:
<Country = {'$(=FirstSortedValue(Country,Date)'}>
The result after the dollar expansion will be an expression such as the following:
<Country = {'New Zealand'}>
You will get a syntax error if you do not use the quotation marks.
Numbers
When you want the dollar expansion to result in a number, ensure that the expansion gets the same formatting as the field. This means that you sometimes need to wrap the expression in a formatting function.
For example:
<Amount = {$(=Num(Max(Amount), '###0.00'))}>
The result after the dollar expansion will be an expression such as the following:
<Amount = {12362.00}>
Use a hash to force the expansion to always use decimal point and no thousand separator . For example:
<Amount = {$(#=Max(Amount))}>
Dates
When you want the dollar expansion to result in a date, ensure that the expansion has the correct formatting. This means that you sometimes need to wrap the expression in a formatting function.
For example:
<Date = {'$(=Date(Max(Date)))'}>
The result after the dollar expansion will be an expression such as the following:
<Date = {'12/31/2015'}>
Just as with strings, you need to use the correct quotes.
A common use case is that you want your calculation to be limited to the last month (or year). Then you can use a numeric search in combination with the AddMonths() function.
For example:
<Date = {">=$(=AddMonths(Today(),-1))"}>
The result after the dollar expansion will be an expression such as the following:
<Date = {">=9/31/2021"}>
This will pick out all events that have occurred the last month.
Example: Chart expressions for set modifiers with dollar-sign expansions
Examples | Results |
---|---|
sum( {$<Year = {$(#vLastYear)}>} Sales ) |
Returns the sales for the previous year in relation to current selection. Here, a variable vLastYear containing the relevant year is used in a dollar-sign expansion. |
sum( {$<Year = {$(#=Only(Year)-1)}>} Sales ) |
Returns the sales for the previous year in relation to current selection. Here, a dollar-sign expansion is used to calculate previous year. |