Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
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

71 Comments
sergejs_kutkovics
Partner - Contributor
Partner - Contributor

Hello all,

First of all the new set expression syntax is awesome, but I got a bit confused with the aggr behaviour

 

aggr(
	(
    	(	{</*[Año], */[Fecha Compra]={"$(vFilterWTD)"}>} sum(EUR)	)	
        /   
        (	{<[Año], [Fecha Compra]={"$(vFilterLWTD)"}>} sum(EUR)	) 	
        - 1
    )
, Agencias)

 

if I uncomment the [Año] field in the first inner expression, I get an Error in expression.

The expression without aggr works as expected:

 

(	{<[Año], [Fecha Compra]={"$(vFilterWTD)"}>} sum(EUR)	)	
/   
(	{<[Año], [Fecha Compra]={"$(vFilterLWTD)"}>} sum(EUR)	) 
- 1

 

and the aggr with one expression works as well:

 

aggr(
    	(	{<[Año], [Fecha Compra]={"$(vFilterWTD)"}>} sum(EUR)	)	
, Agencias)

 

Any thougts how to make it work?

Thanks in advance!

1,520 Views
sergejs_kutkovics
Partner - Contributor
Partner - Contributor

Just a quick update on the previous one:

If I save the inner expression as an variable, it still fails. But if I save the variable as a Master Measure it actually WORKS.

I used the Master Measere as a workaround for now. )))

1,468 Views
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

@sergejs_kutkovics  - it's great to see that you managed to solve the problem using the Master Measure. To me, it looks like a bug in the software that needs to be looked at. It's possible that it was triggered by the "special" character in the field name [Año].

Having said that, let me offer an important distinction, that might explain some of the differences that you mentioned. The following syntax represents the "New Set Analysis Syntax" that this article is about:

{Set} sum(Value)

However, in combination with AGGR(), the set analysis syntax is not so new - placing the Set inside of Aggr() has been possible for a few years:

AGGR( {Set} sum(Value), Dim1, Dim2)

So, while these two Set expressions look identical, in fact they represent somewhat different features that were introduced at difference times - hence a possible reason why one formula might work (without AGGR) and a similar formula may not work the same way (with AGGR).

Using this opportunity, I'd like to invite everyone to my session on Set Analysis and AGGR at the virtual Masters Summit for Qlik, which will take place online on March 1st. I will go through many tricky scenarios like this one, describing advanced uses of Set Analysis and AGGR for developing robust analytics.

 

1,452 Views
sergejs_kutkovics
Partner - Contributor
Partner - Contributor

@Oleg_Troyansky - [Año] is just a year field. I have tested with other fields and it doesn't work as well. And to clarify, the same expression with old style set analysis works as usual. Basically my hypothesis is:

( {Set1} sum(Value) ) / ( {Set2} sum(Value) )       - works
aggr( ( {Set1} sum(Value) ), Dim)       - works
aggr( ( {Set2} sum(Value) ), Dim)       - works
( {Set1} sum(Value) ) / ( {Set2} sum(Value) ) as [Master Measures]   and aggr( [Master Measures], Dim)       - works
aggr(sum({Set1}  Value) / sum({Set2} Value), Dim) - works
and
aggr( ( {Set1} sum(Value) ) / ( {Set2} sum(Value) ) , Dim) - doesn't work if outer set {Set1} has more than one field.

 Hope this helps and thank you for quick reply.

1,428 Views
LauraSc22
Contributor
Contributor

Thanks for this feature. really helps to work with master items. 

I have a question about the TOTAL qualifier .  it cannot be put before the function, right? 

I am trying to create tis measure:

[Revenue]/{<user_Range={'0-29'}>} total<$(UserPeriod)>[Revenue]

When it looks like this it works:

[Revenue]/sum({<user_Range={'0-29'}>} total<$(UserPeriod)> amount)



But I really don't want to open the master items and it would be really nice to have total qualifier  before master items as well. 

1,283 Views
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

@LauraSc22 - this is a great idea! I think you should raise it in the Ideation section and have it voted on.

1,204 Views
sergio_ot
Contributor II
Contributor II

Hello there Qlikers,


I don't understand the behavior of this new functionality when I use the intersection set modifier on the outer part in conjunction with a set modifier (simple assignment) inside. I illustrate an example here:


test outer set analysis merging.png

 

As explained by @hic , when the aggregation function has its own set expression, the context means current selection.

 

In the example, the 3rd and 4th measures have his own set expression, so the inner should have precendence over the outer (if I am not wrong), meaning the results should have been 1 for the 3rd and 3 for the 4th.

Am I missing something?


Regards,
Sergio Otero

943 Views
Lisa_P
Employee
Employee

This is from help:

Inheritance

Inner set expressions have precedence over outer set expressions. If the inner set expression contains a set identifier, it replaces the context. Otherwise, the context and the set expression will be merged.

  • {$<SetExpression>} - overrides the outer set expression

  • {<SetExpression>} - is merged with the outer set expression

So I read that if there is any identifier in the inner set expression, it replaces the context.

906 Views
sergio_ot
Contributor II
Contributor II

Thanks for your response @Lisa_P 

So in the third expression

 

{<VALUE1*={'B'}>} Count({<VALUE2={'X'}>} VALUE1)

 

inner and outer set expressions are merged. How can the result be 3?

857 Views
scottduthie
Partner Ambassador
Partner Ambassador

Hi @robert99 and @hic ,

Firstly, we have found huge benifit in this feature!

However, in response to an earlier conversation on this thread, I also see what apears to be odd/unexpected behavior. And I'm not sure if this is intentional or a bug 🤔

This expression below renders incorrect results - it ignores the '[Business/Consumer]={'Business'}' condition in the outer set becuase within the data model there are no 'Business' values in the field '[Business/Consumer]'. The 'CalendarMTDFlagCY={'1'}' part works fine.

scottduthie_0-1713821185583.png

If I write this using the traditional set analysis syntax and move the '[Business/Consumer]={'Business'}' portion to the inner set, it works as expected (ie. the data model contains no 'Business' values in the field '[Business/Consumer]', and so the KPI calculates as '0')

scottduthie_1-1713821207730.png

It took a lot of trial and error to surface this, and to be honest, it's not a showstopper to work around if we know the intended behaviour, but I can't see anything that mentions this is the documentation. Do you know if this intended behavior?

The use case where this is catching us out is when section access is deployed. Typically, for most users the '[Business/Consumer]' field will contain 'Business' and 'Consumer' values, but for some seleted users, when the model is reduced based on section access the '[Business/Consumer]' does not contain 'Business' values. 

474 Views