Skip to main content Skip to complementary content

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.

Dollar-sign expansion preview in expression editor

Dollar-sign expansion preview in expression editor.

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.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!