Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP 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

69 Comments
hic
Former Employee
Former Employee

@robert99

It works when I try it. How have you defined the "SNSalesEquipAllYrs"?

1,345 Views
robert99
Specialist III
Specialist III

@hic 

Strange. It still doesn't work as I would expect it to.

The below gives the total for 2022. And it works as it should for 1 etc back to YearsAgo = {7}. But not for 8 on as there are no records for YearsAgo = {8} etc

robert99_0-1668575856704.png

The below gives the total for all years. Not zero as I would expect

robert99_1-1668576133626.png

iF however I put YearsAgo = {8} in the  SNSalesEquipAllYrs formula it gives a zero value (as it should)

Its not a big issue for me though. As long as I know it does this

Shown below is the Master Measure.

 

robert99_0-1668577117445.png

 

 

 

 

 

 

 

 

1,313 Views
mk_kmx
Partner - Contributor III
Partner - Contributor III

@robert99 Aren't you referring to the same "catch" as I described here?

Seems to me that what you're describing falls in "Works as designed" category, but this design kind of makes few things impossible, or at least much more complicated...

1,240 Views
sergio_ot
Contributor II
Contributor II

Hello @hic !
Is there a plan to include the aggregation scope (TOTAL) in this outer style, so we can apply totals to master measures? 
Regards, Sergio Otero.

1,226 Views
hic
Former Employee
Former Employee

@sergio_ot 

Ooh, that's a good one. No, there isn't any plan for that. Yet.

But I see the need for it...

1,215 Views
mk_kmx
Partner - Contributor III
Partner - Contributor III

@hic and how about the inverse precedence of set analysis? In other words, outer set analysis overrides inner? That could as well be related to the outer TOTAL.

0 Likes
1,199 Views
robert99
Specialist III
Specialist III

@mk_kmx 

I agree with your example. Although like you I hoped the outer set when using the same field overrode the inner set but can understand why it was done the way it was. And can easily work with how it works

But I think my example above is a bug. I do not have YearsAgo in the inner set and it works fine until YearsAgo = 8 (an option that should give a zero balance as 8 years back was not in any table). We only loaded data from 7 years back on. 

 

1,178 Views
paulcalvet
Partner - Specialist
Partner - Specialist

Hello, @hic@Lucke_Hallbergson 

Is it work with alternative state ? 

I try this expression but it doesn't work 

{<[Brand site]=[Comparison]::[Brand site]>} Sum({<Rolling={1}>} [Line total amount after discount incl. tax]/[Exchange rate])

This expression works :

Sum({<Rolling={1},[Brand site]=[Comparison]::[Brand site]>} [Line total amount after discount incl. tax]/[Exchange rate])

Thanks

987 Views
Lucke_Hallbergson

@paulcalvet 

Yes it does.

In what way is it not working as expected for You?

Example using Ctrl00 app and Bookmark as defined below and example of inner and outer Set Analysis applied to KPI objects.

state_bookmark.png
Are You using an app that You can share?

Can this "[Brand site]=[Comparison]::[Brand site]" not be replaced by "[Comparison]::[Brand site]" only?

BR
Lucke

0 Likes
959 Views
paulcalvet
Partner - Specialist
Partner - Specialist

Hello @Lucke_Hallbergson 

Many thanks for your feedback.

It works with {<[Brand site]=[Comparison]::[Brand site]>} Sum({<Rolling={1}>} [Line total amount after discount incl. tax]/[Exchange rate])

I had a $ in my previous expression {<[Brand site]=[Comparison]::[Brand site]>} Sum({$<Rolling={1}>} [Line total amount after discount incl. tax]/[Exchange rate]) and that's why it doesn't work.

This new feature is very useful and it's an incredible time saver !

Regards,

936 Views