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

Example - chart expressions

Load script

Load the following data as an inline load in the data load editor to create the chart expression examples below.

Let vToday = Today();
MyTable:
Load 
Year(Date) as Year,
Date#(Date,'YYYY-MM-DD') as ISO_Date, 
Date(Date#(Date,'YYYY-MM-DD'),'M/D/YYYY') as US_Date, 
Country, Product, Amount 
Inline
[Date, Country, Product, Amount
2018-02-20, Canada, Washer, 6
2018-07-08, Germany, Anchor bolt, 10
2018-07-14, Germany, Anchor bolt, 3
2018-08-31, France, Nut, 2
2018-09-02, Czech Republic, Bolt, 1
2019-02-11, Czech Republic, Bolt, 3
2019-07-31, Czech Republic, Washer, 6
2020-03-13, France, Anchor bolt, 1
2020-07-12, Canada, Anchor bolt, 8
2021-10-15, France, Washer, 1];	

For more information about using inline loads, see Inline loads.

Chart expressions with dollar-sign expansions

Create a table in a Qlik Sense sheet with the following chart expressions.

Table - Set modifiers with dollar-sign expansions

Country	Sum (Amount)	Sum({<US_Date={'$(vToday)'}>} Amount)	Sum({<ISO_Date={"$(=Date(Min(ISO_Date),'YYYY-MM-DD'))"}>} Amount)	Sum({<US_Date={">=$(=AddYears(Max(US_Date),-1))"}>} Amount)
Totals	41	1	6	1
Canada	14	0	6	0
Czech Republic	10	0	0	0
France	4	1	0	1
Germany	13	0	0	0

Explanation

• Dimensions:

  • Country

• Measures:

  • Sum(Amount)

    SumAmount with no set expression.

  • Sum({<US_Date={'$(vToday)'}>}Amount)

    Sum Amount for all records where the US_Date is the same as in the variable vToday.

  • Sum({<ISO_Date={"$(=Date(Min(ISO_Date),'YYYY-MM-DD'))"}>}Amount)

    Sum Amount for all records where the ISO_Date is the same as the first (smallest) possible ISO_Date. The Date() function is needed to ensure that the format of the date matches that of the field.

  • Sum({<US_Date={">=$(=AddYears(Max(US_Date),-1))"}>}Amount)

    Sum Amount for all records that have a US_Date after or on the date a year before the latest (largest) possible US_Date. The AddYears() function will return a date in the format specified by the variable DateFormat, and this needs to match the format of the field US_Date.

For more information about expressions, see Using expressions in visualizations.