Qlik™ Set Analysis - Redux
Qlik™ Set analysis feature is a powerful data filtering and aggregation technique that allows users to create custom data subsets for analysis. It enables users to define complex criteria, known as set expressions, to isolate specific data points or dimensions within their Qlik™ applications. This feature is instrumental in performing advanced data manipulation, and it just got even easier with Qlik™’s new AI enhancements.
Things you should know about Set Analysis!
- There is a TON of information on this already
- Originally it was a WRITTEN Syntax – now easier to use!
- It is an EXPRESSION used in MEASURES to support data analysis
- It is used in Chart Objects and NOT in the Data Load Script
- Think of it as a conditional aggregation
- Used to limit values to a set of data, different than the current selection
- Used with Aggregation function of Measures
- Can be dynamic – used with Variables $() and Functions ()
- Improvements have been implemented
Why to use Set Analysis?
- Hardcoded Conditions
- Calculated Percentages
- Comparative Analysis (Time periods)
- Ignoring Selections
- Looking for all possible and excluded values
- Comes in handy with multiple calendars and point in time
Set Expression Structure
Identifiers – Represents a selection, a specific set of records
- Current selection
- Bookmark
- Alternate state
A simple set expression consists of a single identifier, such as the dollar sign, {$}, which means all records in the current selection.
Example:
$,1 $1, $_1, BookMark1, State2
Operators – Used to create unions, differences or intersections between different set identifiers.
This way, you can create a subset or a superset of the selections defined by the set identifiers.
Example:
+,*,-,/ (union, intersection, exclusion, Symmetric difference (XOR)
Modifiers – Added to the set identifier to change its selection.
Exemplu:
<Year={2020}>,<Supplier={ACME})
SUM({1<Year-={‘2023’}>}Sales)
Example of a Set Expression
SUM({1<Year={‘2023’}>}Sales)
SUM({<Year={‘2023’}, Category={‘Babywear’}>}Sales)
SUM({<[Year]={“$(=Year(Today())-1)”}>}Sales)
Sum({<[OrderID]=P({<[OrderID]={“=SUM(Quantity)>=$(vQuantityScat
) AND SUM(Sales)>=$(vTotalSalesScat)”}>}OrderID)>}Sales)
SUM({<CategoryName={“Men*”}>}Sales) — search
SUM({<CategoryName={‘Men*’}>}Sales) — explicit
What’s Improved and New at Set Analysis
Simplify & Reuse
- New Syntax Structure to make it easier:
- Additional position for the Set Expression
- Master Measure support {<Year={2021} [Master Measure]
- Scoping using () to control evaluation
- Context – current selection OR Outer Set Expression
- New Expression Generator
- Master Calendar Measures
- Expression Editor (Bookmarks, Alt states, selections, preview)
This expression
Sum=({<Year={2021}>} Amount)
Can be this
{<Year=2021>} Sum(Amount)
Set expression is applied to both
{<Year={2021}>} Sum(Amount) / Count(distinct Customer)
How is it made easier?
Point and Click
- Expression Editor
- Insert Selections
- Bookmarks and Alternate States
For more details and demonstrations, please watch the video below!
Set analysis is one of the more powerful tools you can use in Qlik Sense™ and QlikView™. Its syntax is sometimes perceived as complicated, but once you learn it, you can achieve fantastic things. There is now an additional way of writing the Set expression, that may simplify your code.
Set analysis is a way to define an aggregation scope different from current selection. Think of it as a way to define a conditional aggregation. The condition – or filter – is written inside the aggregation function.
For example, the following will sum the amounts pertaining to 2021:
Sum({<Year={2021}>} Amount)
This syntax however has a couple of drawbacks:
- First, it is not easy to combine a master measure with different set expressions, since the set expression is hard-coded inside the master measure.
- Secondly, if you have an expression with multiple aggregations, you need to write the same set expression in every aggregation function.
Therefore, we introduce an additional position for set expressions: They can now be written outside the aggregation function and will then affect all subsequent aggregations. This means that the below expression is allowed:
{<Year={2021}>} Sum(Amount) / Count(distinct Customer)
For master measures, this change will allow a very powerful re-usability: You can now add set expressions to tweak existing master measures:
{<Year={2021}>} [Master Measure]
Lexical scoping
The outer set expression will affect the entire expression, unless it is enclosed in round brackets. If so, the brackets define the lexical scope. For example, in the following expression, the set expression will only affect the aggregations inside the brackets – the Avg() call will not be affected.
( {<Year={2021}>} Sum(Amount) / Count(distinct Customer) ) – Avg(CustomerSales)
Position
The set expression must be placed in the beginning of the lexical scope.
Context and inheritance
Aggregation functions that lack set expression, will inherit the context from the outside: In earlier versions the context was always defined by the current selection. Now it has been added the possibility of having the context defined by a set expression. So, now “context” means current selection or an outer set expression.
Inner set expression
If an aggregation function already contains a set expression, this will be merged with the context. The same merging rules as today will apply:
- An inner set expression with a set identifier will NOT inherit from the context. It will inherit the selection from the set identifier instead.
- An inner set expression that lacks set identifier – it has only a set modifier – will inherit from the context.
- How the merge is made depends on the set assignment for the field; whether it is made with an equal’s sign “=” or with an implicit set operator, e.g. “+=”. The logic is identical to how current selection is merged with a set expression.
Examples
{<OuterSet>} Sum( {<InnerSet>} Field )
The OuterSet will be inherited into the InnerSet, since the inner set lacks set identifier.
{<OuterSet>} Sum( {$<InnerSet>} Field )
The OuterSet will not be inherited into the InnerSet, since the inner set expression contains a set identifier.
Aggr()
The set expression of the outer aggregation will never be inherited into the inner aggregation. But a set expression outside the outer aggregation will be inherited into both.
Examples
Sum({<Set1>} Aggr(Count({<Set2>} Field )))
The Set1 will not be inherited into Set2.
{<OuterSet>} Sum({<Set1>} Aggr(Count({<Set2>} Field )))
The OuterSet will be inherited into both Set1 and Set2.
Summary
Nothing changes for existing set expressions – they will continue to work. But with this additional syntax we hope to simplify your work and your expressions and allow you to re-use your master measures more effectively.
This change affects all Qlik Sense™ editions starting with the August 2022 release. The following was included in the major QlikView™ release this spring.
For information about Qlik™, please visit this site: qlik.com.
For specific and specialized solutions from QQinfo, please visit this page: QQsolutions.
In order to be in touch with the latest news in the field, unique solutions explained, but also with our personal perspectives regarding the world of management, data and analytics, we recommend the QQblog !