As you have seen in the previous recipes, we have used functions, such as Max()
and GetFieldSelections()
inside our Set Analysis. Embedding the functions inside a Set Analysis expression, specifically in the rules area that defines the set of records we want to calculate across is known as dollar sign expansion.
Defining a set of records in the simplest literal form is as follows Year= {2015}
.
The expression needs to know the year you want to use, dollar sign expansion allows us to generate the text dynamically. Understanding how to use dollar sign expansion in your Set Analysis expressions enriches the amount of analysis you can perform. Sometimes just using the function alone or specifying literals in Set Analysis is either too time consuming or adds unnecessary maintenance to the application.
For the purpose of this recipe, we make use product sales data as defined in the following script. Load the following data into the Qlik Sense data editor:
Transactions: Load date(today()-IterNo()) AS Date, Pick(Ceil(3*Rand()),'Standard','Premium','Discount') AS ProductType, floor(date(today()-IterNo())) AS DateNum, Round(1000*Rand()*Rand()*Rand()) AS Sales Autogenerate 1000 While Rand()<=0.9 or IterNo()=1;
Total Sales
:sum(Sales)
WTD
:sum({<DateNum={">=$(=Today()-7)"}>}Sales)
Previous WTD
:sum({<DateNum={">=$(=Today()-14)<$(=Today()-7)"}>}Sales)
Weekly Variance
:(COLUMN(2)-COLUMN(3))/COLUMN(2)
When calculating something like week to date sales, the set of records you identify in your Set Analysis expression will change every day. When you use functions such as Today()
inside the Set Analysis expression, the literal text values that the expression uses change automatically. Ultimately using dollar sign expansion is just a replacement for the text strings that you could use.
If the date today is 06/08/2015
then. The user see the set condition as:
DateNum={">=$(=Today()-7)"}
While Qlik Sense sees the set condition as:
DateNum={">=31/07/2015"}
This is because the function inside the dollar sign is evaluated first and it simply expands into the text/field values that we want to calculate across.
The fourth expression is written as:
(COLUMN(2)-COLUMN(3))/COLUMN(2)
. Here we pick up the column numbers instead of the actual field names for our calculation.
We can also write the expression in the following manner:
([WTD]-[Previous WTD])/[WTD] .
We will get a bad field name: ([WTD]-[Previous WTD])/[WTD]
at the bottom of the Expression editor window. But don't worry, as Qlik Sense will still interpret the results correctly. This chink may be ironed out in future releases of Qlik Sense.
The expression does not make use of the fields that we have loaded into the applications data model. It instead uses the expression labels we have already created for the previous calculations. This is always a best practice option if you need to use the same calculation in the same table more than once. It make things simpler, you only have to change something once and best of all it is optimized and the calculation is already cached in RAM.