Suppose a KPI displays Total Sales across all years. Now, if you make a selection on Year (say 2022), the KPI will consequently display Sales for 2022.
But what if you wanted a KPI to display sales for 2022 without ever selecting it, or wanted a KPI to always display Total Sales across all years regardless of any selections made?
This is where Set Analysis comes into play. It is a powerful front-end tool that allows one to create a custom subset of data that differs from the actual selection.
Let's explore how Qlik Sense Set Analysis can help you tailor your data visualization to your exact needs.
Set Analysis is the analysis performed on a data subset after a certain expression is written. This expression is known as the Set Expression.
The Set Expression syntax can be really daunting, however, this article will help you navigate through it with ease.
A Set Expression can be written in two ways:
Inner Set Expression
Outer Set Expression
In an Inner Set Expression, the set expression is added inside an aggregation function.
The bold text is the Set Expression. It translates to ‘just choose products with the name Laptop.’
{ } → Curly Braces are used to initiate ({) and end (}) the Set Expression.
< > → The Set Modifier comes within these symbols. In the above example, Product = {‘Laptop’} is the set modifier.
In an Outer Set Expression, the set expression precedes the aggregation.
The terminologies are akin to the Inner Set Expression.
In the case of Outer Set Expression, all the fields (Sales, Years) in the above will inherit the Set Expression.
A generic Set expression contains the following components.
Let's explore these in detail.
Set Identifiers are used to represent the Selection Criteria that the Set Analysis should consider. They need to be mentioned right after the opening curly brace ( { ).
The below table summarizes all the Selection Criteria that can be used.
This Set Identifier is used to ignore all the selections made in the dashboard, i.e. it's used when a static value is to be shown.
Question:
Imagine you are working with a sales dashboard in Qlik Sense, and your organization wants to track and visualize the performance of a specific product category (e.g., “Electronics”) compared to the overall sales across all product categories.
Solution:
According to the question, we need 2 KPIs:
The first one that shows the Total sales including all product categories, disregarding selections.
The second one that shows the Sales of a selected product category.
Following is the Set Expression for the first and second KPI:
Notice how the KPI on the left doesn’t change even though a selection has been made, while the second KPI reflects the changes according to the selection.
The Set Identifier {$1} is used to get the value of the forward selection, while {$_1} is to get the value of the previous selection.
Similarly, {$2} means go two selections forward while {$_2} means go two selections backward, and so on.
In other words:
{$1} is akin to the ‘Step Back’ Button.
{$_1} is akin to the ‘Step Forward’ Button.
Set Identifier {$} refers to the current selection. It is the default identifier, meaning an explicit mention isn’t necessary.
Below is the Set Expression along with the output.
In essence, Set Identifiers in the Qlik Sense, including {1}, {$1}, and {$_1}, play a pivotal role in shaping data analysis by allowing users to control the context of selections. Utilizing these identifiers, users can create dynamic KPIs, such as tracking overall sales regardless of selections ({1}), retrieving values from forward or backward selections ({$1}, {$_1}), and referencing the current selection state ({$}).
Set Operators are operations performed on two or more subsets of data to obtain a single set, i.e. the combination of those sets.
There are 4 Set Operators in Qlik Sense Set Analysis:
It simply produces a union of two sets.
Question:
Suppose you are managing a financial dashboard in Qlik Sense, and your company is interested in monitoring country-wise sales. The objective is to design a visual representation that illustrates sales for those countries that begin with either an ‘S’ or end with ‘land’.
Solution:
We require two sets according to the question:
Set 1 → Containing countries beginning with an ‘S’.
Set 2 → Containing countries ending with ‘land’.
Logically, we will have to combine Set 1 and Set 2 i.e., take a union of both sets.
Below is the code to achieve it, along with the output.
The Left KPI shows total sales for country names beginning with an ‘S’ and ending with ‘land’ while the Right Table is to verify the total sales of the Left KPI.
As the name suggests, it chooses the common values between two sets and ignores the rest.
Question:
Calculate the total sales for the intersection between a Bookmark and the Current Selection.
Solution:
Below is the code.
This operator excludes the selections made, meaning values not present in the selection are considered.
The below representation will elaborate on the above statement!
As you can see, when HR is selected the unselected values i.e. Engineering, Finance, and Sales are displayed in the Right Text Box.
Question:
Create a functionality where the manager can visualize the total sales of those customers that are not included in a selection.
Solution:
Below is the code and its result.
This functionality will help if there are numerous values in a field and only a few are to be excluded. Therefore, the few that aren’t required can be excluded by selection.
Symmetric Difference basically means the complement of an Intersection.
Question:
Let's apply the symmetric difference operator to the question mentioned in the Intersection Set Operator section.
Below is the code:
In summary, Qlik Sense Set Analysis employs four key set operators: Union, Intersection, Exclusion, and Symmetric Difference. These operators enable precise data analysis, allowing users to combine, extract, and visualize subsets based on specific criteria, enhancing the flexibility and depth of insights within financial dashboards and analytics scenarios.
Set Modifier is the crux of a Set Expression, thereby, making it a vast topic. However, to simplify it, let’s break it down into several points.
Set Modifiers make modifications to the current selection.
They are added to the Set Expression after the Set Identifier.
Modifications are made using field names.
A search mask enclosed in curly braces ‘{ }’ and is given to the Right Hand Side of the field name separated by an equal sign ‘=’.
The search mask filters out all the values within the field.
The search mask could be one of the three:
Numeric Value:
A value in the field:
A formula/ Qlik function/ Wildcard:
The Set Modifier needs to be placed within ‘< >’.
There can be more than 1 Set Modifier, for example:
Element Functions are used when a nested set expression is required. There are two types of Element Functions:
P( ) → Possible Values
E( ) → Exclude Values
Let’s take a look at an example for each.
Question:
Calculate the total sales of customers that have purchased a Laptop.
Solution:
At first glance, this question may seem benign. So let's solve it as it may seem.
Below is the benign code:
In reality, the above code will produce the total sales for Laptops (4.8k), however, we require the total sales of customers who have purchased a laptop (6.4k).
So let's break down the question into two parts.
Find Customers who have purchased a Laptop. Below is the set expression to achieve it.
Find the total sales made by those customers. Suppose the above code returns customer names as “Abdullah”, “Gaurav”, and “Siddesh”. Then the set expression will be:
Now, when we merge both codes, we get:
Do the same for Excluded Values, i.e., E() and you will get the total sales of customers who haven’t purchased a Laptop.
Below is the code along with the output snippet.
Mastering the fundamentals of Qlik Sense Set Analysis opens the door to powerful data manipulation and visualization capabilities. Set Analysis serves as a vital tool for tailoring data representations precisely to meet specific requirements.
Understanding the syntax, identifiers, operators, modifiers, and element functions empowers users to create dynamic visualizations that remain unaffected by user selections or provide targeted insights based on varied criteria.
The ability to employ set operators like union, intersection, exclusion, and symmetric difference significantly enhances data analysis possibilities.
