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;
- Create a new sheet and double click on the table object to add it to the main content area.
- Add Product Type as a dimension.
- Add the following measure as an expression, label it
Total Sales:sum(Sales)
- Add the following measure as the second expression, label it
WTD:sum({<DateNum={">=$(=Today()-7)"}>}Sales) - Add the following measure as the third expression, label it
Previous WTD:sum({<DateNum={">=$(=Today()-14)<$(=Today()-7)"}>}Sales) - Add the following as the fourth expression, label it
Weekly Variance:(COLUMN(2)-COLUMN(3))/COLUMN(2)
- For the expression in step 6, change the number formatting to Number and then select the percentage format from the drop-down list.
- You should have a table that looks similar to the following image. The figures may not be similar to the following image as we are using the Rand function to generate the initial set of data in the script:
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.