When talking about Advanced Expressions in Qlik, most people will refer to Set Expressions. Set Expressions are Qlik’s proprietary advanced expression language. Unfortunately, because of this reason it can be rather overwhelming for new or causal Qlik users.
Therefore, I thought I’d try and provide a straight-forward guide with use-cases and examples for implementing this extremely useful expression language. But first, we will start from the very beginning.
If you have built an expression you will hopefully recognise the basic syntax:
Aggregation([field])
e.g. Sum([Sales Amount])
Here you will have a field which you want to aggregate over, whether it be a Sum (adding all values), Count (calculate the frequency of values), or alternative aggregation. As you may have guessed, this syntax can then be extended:
Aggregation([{SetExpression}] [DISTINCT] [TOTAL] [field])
Despite the DISTINCT and TOTAL keywords not technically forming part of Set Expressions, I will cover these first as they have as much of an impact on a given expression. The DISTINCT keyword forces the expression to ignore duplicate values. The most common use-case for DISTINCT is within a count. Given the data below we would retrieve two different results using the DISTINCT count of customers compared to if we didn’t.
A DISTINCT count on Customer number would return 4, representing the total number of unique customers. A non-distinct count would return 10; the number of non-null values within the table.
The TOTAL keyword on the other hand forces the expression to ignore the chart dimensions, therefore is less relevant in visualisations without dimensions i.e. KPI and text & image objects. This is useful when you want to calculate the proportional split of an expression over a dimension, for example:
Proportion expression:
SUM(Sales) / SUM(TOTAL Sales)
For more information on advanced expressions or how you could unlock the full potential of Qlik Sense, please get in touch with us – either directly or through our social channels.
You can stay up-to-date with all the latest Qlik news, tips and tricks by following us on LinkedIn or Twitter and subscribing to our YouTube channel.
By Chris Lofthouse
Topic: