Following on from my last post on Advanced Expressions in Qlik, in this post I’m going to cover two concepts – how to force an expression to ignore all sections and how to override/predefine selections within an expression.
Before we begin it would be worth your while familiarising yourself with this table of data:
Advanced Expressions – Ignoring Selections
If you have used Qlik’s Set Expressions feature, or our master measure tool at OmetisToolkit.com, you may have come across something like this ‘{$}’ in the expression it builds for you i.e. SUM( {$} Sales). Well, this is a Set Expression. Set Expressions start and finish with curly braces. In the example above the dollar ‘$’ represents the current selections state. Therefore, SUM( {$} Sales) and SUM(Sales) are exactly the same, as all expressions are by default assigned to the current selections state. This means that every time a selection is made the expression is recalculated and the value adjusts accordingly.
To force an advanced expression in Qlik to ignore selections we must redefine the relationship between the expression and the selection state. To do this we simply pass the value ‘1’ into the curly braces. For example:
SUM( {1} Sales)
The expression above will evaluate the sum of sales for the entire data set, and disregards all selections made by the user. The name given to the relationship between the expression and the selection state is ‘Identifiers’ – as they identify the state the expression should use. It is worth noting that identifiers ignore selections and not chart dimensions like the TOTAL Keyword. You can use both of these in tandem to calculate across the entire data set, ignoring the dimension value in the visualisation and selections made. This is useful when you want to calculate weighted values.
For example, let’s say I filtered the previous table of data to product ‘A’:
In the table above, we have the first calculated expression (second column) showing the sum of sales by customer number, for product ‘A’. Next, we have the sum of all sales by customer, regardless of selections. In the third calculated expression column, we have the total sum of sales for product ‘A’, regardless of the dimension (Customer no.). And lastly, we have the total sum of sales, regardless of the dimension and selections. Things just got interesting! Using combinations of these expressions enables Qlik users to perform more advanced expressions such as the percentage of total sales.
Advanced Expressions – Override/Predefine Selections
To override/predefine selections you have to modify the selection state, hence the technical term for this is called modifiers. The syntax for modifiers is as follows:
AGGREGATION({ IDENTIFIER <[field={‘Value1’}>} [field])
Like with the identifiers, you may have seen this in action before with the tools now available. To implement modifiers, you must wrap them in chevrons ‘<>’, then specify the field you want to define a filter followed by ‘=’ and wrap your value in a set of curly braces. You can provide numerical and string values, the latter are required to be passed in single quotes. You can also provide multiple values using a comma separated list i.e.:
SUM({$<Product={‘ProductA’, ‘ProductB’>} Sales)
The expression above calculates the sum of sales for just Products A and B, regardless of the selection in the product field. The expression is also defined to the default selection state, therefore if I filter the dataset on another field such as Region A, the expression would return the sum of sales for Products A and B that occurred in Region A. It only modifies the selection state for the fields specified between the chevrons. You can also list multiple modifiers, separating them with a comma. A common use-case for this is time, for example limiting a figure to the current year and/or previous year.
If you use the Ometis Time aware calendar generator then you’ll know it creates a report period field containing common time descriptions. This makes selecting the current year and last year a breeze:
When it comes to advanced expressions in Qlik, I hope this has been useful. Should you want to know more or want to request any new video examples please get in touch and remember to checkout the latest tools/content at Ometis Blog and OmetisToolkit.com.
By Chris Lofthouse
Follow @clofthouse89
Topic: Data analytics
Comments