New Set Analysis syntax

[hic]

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.

[hic]

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 we have 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 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.

See more on
https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

HIC

[marcus_sommer]

IMO it's not a bug and behaved like intended respectively in regard to the underlying technically implementation of adding/overwriting/ignoring all/certain parts of a selection state respectively combining multiple selection states.

To express it in other and simpler words - it are selections. If the data-model respectively the associations between the data and the available data-set in regard to the existing fields and field-values doesn't support the wanted selections they aren't also not applicable within a set analysis. Like a selection doesn't returned any error or wrong results if the chosen values could be added to the selection state or not respectively if this selection removed other selections from the selection state - it wouldn't happens by a set analysis. You get what you select.

Set Analysis is an extremely powerful feature which has of course some complexity. The new addition of defining an outer set statement and/or combining it with inner set statements is not a simplification and general applicable else the opposite and extending the possibilities by increasing the complexity. You are right, if you missed here appropriate hints, explanations and examples within the documentation but it's not very surprising because nowadays the tools could everything do without any complexity and the results are always simple and correct ... and AI will save the world ...

Going further a set analysis is quite often overused as replacement for saving efforts in the development of a data-model or as usability within the UI. In this regard I like to refer to this great posting: Let the User Select - Qlik Community - 1463978

[BartBrouwer]

@hic From what QlikView version onwards is the usage of outer set expressions supported? Couldn't find it in the release notes

[hic]

It ought to be May 2023 - 12.8, but to be sure you need to check with Qlik support.

HIC

[evanplancaster1]

Super late to the party here, but...

@mk_kmx your comment about using variables to store the set expressions is really powerful. I stumbled upon that idea some time ago (I shoulda read the comments to this post more closely!), and we are starting to implement it at my company now.

The really cool thing about this strategy is that those set expression variables are "stackable", meaning you can do things like, e.g., $(fYTD)$(fUSOnly)[Sales], where:

• [Sales] is a master measure = sum(Sales)
• fYTD is a variable containing this as the expression:  {<[YTDFlag]={1}>}
• fUSOnly is a variable containing this as the expression: {<[CountryCode]={'US'}>}.

Thus, $(fYTD)$(fUSOnly)[Sales] will give you all sales in the United States YTD based on the year(s) you've selected.

This stacking strategy effectively allows developers to abstract away the complexities of set analysis syntax that might confuse non-power users. In other words, it basically creates a list of "master" set expressions, which is what @qliktime was driving at, I think.

An added benefit is that it allows devs to store the business logic found in those "set expression" variables into a centralized repository if desired (we're using a database, but you can use spreadsheets or even QVDs if you want) so they can be accessed via the load script in any apps that need them (assuming they have field names that are identical, of course). 

Need to change a piece of business logic? Instead of having to go to every app that uses it and locate and change every instance of that set expression, change it one time in the repo. The next time all your apps reload, the set expression will be updated in every app that uses it, in every place it occurs, automagically.

Need to create a new app that will use many of the same fields and much of the same business logic as another app already in production? Pull in all the necessary variables from the repo via the load script, and then you can build out your measures MUCH faster because you only have to invoke the $-expanded variable for each set expression instead of having to reinvent the wheel for the syntax of your set analysis; International Sales Last Year-To-Date is simply:

$(fYTD)$(fLY)$(fNonUSOnly)[Sales]

No worries about missing curly brackets or quotes, no worries about the same measure being built 6 different ways, no worries about locating that esoteric logic in that one object on that one sheet in that one app that no one can ever remember where it is so you can copy/paste it into a new measure.

Outer set analysis really is a game changer!

(NOTE: all of this assumes no States are being used -- alternate OR default. The inheritance rules get really tricky in those instances, as has been documented elsewhere in these comments, and I'm still trying to wrap my head around how all that works.)

Belated thanks, @hic , for this article!