Sometimes you may have groups of expressions you want to view that either they don't need to be viewed at once or you don't have the room to display them all. In these cases you do not have to go and create another sheet, you can add a control to let users select what is calculated.
The output of this recipe is similar to the preceding recipe, only with slightly different expressions to add depth of analysis in the same object.
For the purpose of this recipe, we make use product sales and margin data as defined in the following script. Load the following data into the 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,
Round(10*Rand()*Rand()*Rand()) AS Quantity,
Round(Rand()*Rand(),0.00001) AS Margin
Autogenerate 10000
While Rand()<=0.9 or IterNo()=1;
Measures:
LOAD * INLINE [
Measures
Sales
Quantity
Margin
];- Create a Filter pane object and add Measures as the dimension.
- Next, drag across a table object onto the main content area.
- Add Product Type as a dimension
- Add the following expression as the first measure and label it
Total Sales:sum($(=GetFieldSelections(Measures)))
- Add the following expression as the second measure and label it
WTD:sum({<DateNum={">=$(=Today()-7)"}>}$(=GetFieldSelections(Measures))) - Add the following measure as the third expression and label it
Previous WTD:sum({<DateNum={">=$(=Today()-14)<$(=Today()-7)"}>} $(=GetFieldSelections(Measures))) - Add the following as the fourth expression and label it
Weekly Variance:(COLUMN(2)-COLUMN(3))/COLUMN(2)
- For the expression in step 7 change the number formatting to Number and then select the percentage format from the drop-down list.
- If you come out of the Edit mode and select one value from the Filter pane object, you can see the calculation changing.
- You should have a table that looks similar to the following image. The figures may not be exactly similar to the following image since we are using the Rand function to generate the initial set of data in the script:
Here we capture the field values that we want to calculate using a data island. When we use a data island, we simply pick an option from the measures box without filtering the data in any way. But this approach allows us to control what calculations are being returned.
The GetFieldSelections (Measures) function simply returns Sales, Margin, or Quantity depending on what you have selected. As such, writing the expression Sum (GetFieldSelections (Measures)) means we can have any of the three options displayed just by selecting the value from the Filter pane.
As mentioned in the previous recipe, we can write the Weekly Variance expression using the expression labels previously defined in the table as follows:
([WTD]-[Previous WTD])/[WTD]
We will get a warning for "Bad field" at the bottom of the Expression editor window. Ignore it, as this chink may be ironed out in future releases of Qlik Sense.