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 ];
Total Sales
:sum($(=GetFieldSelections(Measures)))
WTD
:sum({<DateNum={">=$(=Today()-7)"}>}$(=GetFieldSelections(Measures)))
Previous WTD
:sum({<DateNum={">=$(=Today()-14)<$(=Today()-7)"}>} $(=GetFieldSelections(Measures)))
Weekly Variance
:(COLUMN(2)-COLUMN(3))/COLUMN(2)
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.