Skip to main content
hic
Former Employee
Former Employee

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

69 Comments
Lucke_Hallbergson

@RoryMcHugh-Catalyst , @sergio_ot , @robert99@QlikService

Engine 12.1505.0 is now deployed and You should not need to use the ()-workaround anymore!

We're sorry for the inconvenience!

Regards
Lucke

1,086 Views
RoryMcHugh-Catalyst
Partner - Contributor II
Partner - Contributor II

Thanks @Lucke_Hallbergson  and team for the quick fix

I've made a few checks and things look back to normal again 

Glad that we can continue using the new syntax 😄

1,064 Views
robert99
Specialist III
Specialist III

Hi @Lucke_Hallbergson 

Checked the issue I had (and the QlikService one) and everything seems fine

Thanks for the prompt action

 

 

 

1,015 Views
yaroslav
Partner - Contributor II
Partner - Contributor II

Hello.

This is useful functionality.

There is a case where I can't understand the logic.

Why is the result of expression Sum({<Dim1={A}>} total Expression1) not  equal to the result of {<Dim1={A}>} Sum( total Expression1)?

yaroslav_0-1666170740211.png

 

994 Views
Lucke_Hallbergson

@yaroslav
This is my result with a ctrl-00 app as of right now (same result with both notations);
example.png

Are You sure that the expression in colum 5 is what the label says and not just "Sum(total Expression1)"?

Like this -

 couldthisbeit.png


BR

Lucke

933 Views
yaroslav
Partner - Contributor II
Partner - Contributor II

@Lucke_Hallbergson  Thank you for your comment.

New synaxis is working correctly in qlik cloud environment.

I took the example from Qlik Sense Enterprise on Windows (August 2022 Patch 1, 14.78.7)

0 Likes
910 Views
Lucke_Hallbergson

@yaroslav thanks for clarification!

Yes, my example was from cloud.

QSEfW needs an updated engine to fix this (bug has has been flagged for inclusion).

Regards
Lucke

900 Views
anders-adv
Partner - Contributor
Partner - Contributor

Hi Lucke.

Did that fix end up in the QSEfW November 2022 release?

BR

/Anders

748 Views
robert99
Specialist III
Specialist III

Hi @Lucke_Hallbergson 

I've found what seems to be another bug

{<YearsAgo = {b}>}
SNSalesEquipAllYrs

I would have thought the above would give a zero balance. Rather it gives the balance as if the set analysis {<YearsAgo = {b}>}  didn't exist. Even if I put in

{<YearsAgo = {10}>}
SNSalesEquipAllYrs

It still gives all years unless YearsAgo contain 10 as an option

Also

{<YearsAgo *= {2}>}
SNSalesEquipAllYrs

The *= doesn't work as it should (it seems) as well. If a filter for YearsAgo is selected for say 3 this ie YearAgo = {2}  reverts to showing all years. Rather than a zero balance

716 Views
Lucke_Hallbergson

@anders-adv 

Yes it did.

BR

//Lucke

683 Views