Advanced Expressions In Qlik (Distinct and Total) - Ometis
Qlik

Advanced Expressions In Qlik (Distinct and Total)

Posted on: 16th July 2019, 9:57 am

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])

Distinct Keyword

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.

Qlik Sense DISTINCT Keyword example
Count vs count DISTINCT example.

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.

Total Keyword

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)

Qlik Sense TOTAL keyword example.
Sum vs Sum TOTAL example.

 

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

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Get the most from Qlik Sense

Our range of Qlik Sense add-ons will enable you to get the most out of your platform.