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:
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.
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 we have added the possibility of having the context defined by a set expression. So, now “context” means current selectionor 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 equals 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 from the August 2022 release. It will also be included in the next major QlikView release, planned for late spring 2023.
I wasn't too sure how big a difference this would make. Until I looked at how much easier the measure would be in one Financial App I did. This included a traditional P+L account
It can make a huge difference.
Even without the master measure (PLActMthCustmise) this change to SA can simplify an expression (see at bottom as an edit)
Example for a YTD measure for a traditional P+L. (And not the most complex example)
EDIT. Even without using a master item this update makes a big difference. This is a brilliant update to Set Analysis
The statements are much easier to read now with the repeating SA filters at the start. Its also easier to copy these into other measures as required. With less likehood of making a copying error.
This sounds like a great first step - hopefully it's the precursor to chart-level and sheet-level set analysis that will be applied to all measures within that chart or sheet in a manner similar to states, which would reduce the need for manual expressions.
@AlexOmetis Perhaps I could if I was a SaaS user, which I am not... 🙂
Given the new syntax, it seems pretty logical to add a set option for both objects and sheets where Qlik would attempt to apply it to all measures in that object or sheet. From the post you linked, it seems the existing SaaS option filters the hypercube, which is different from applying a set expression to the measures (it has both advantages and disadvantages, depending on what one is trying to achieve).
So you want set analysis attached say to a sheet. So it automatically applies to all Charts and tables in the sheet.
This would totally change the way QS is done at present. I like the simplicity of Qlik. And how long does it take to copy and paste one set of SA to say 5-10 charts or tables (esp now as a SA statement can be applied at the very beginning of the measure). At least in this way the chart creator can easily check the measure without having to consider sheet level SA.
But if this option was popular I would add it to the new Simplified Authorising Experience. At least then the filters appear on each chart or table. Leave SA to only apply to one chart or table.
When is this going to be available? You mentioned at the end that Aug 2022 release onwards will be affected but not sure what does that mean? Would it be Aug 2022 Patch 2?
.png "Former Employee"){kind=icon}