Announcements
Former Employee

## New Set Analysis syntax

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.

HIC

Partner - Contributor III

I like this a lot at first blush - I think it will be even more meaningful to Qlik developers than my previous favorite, which was master measures by reference.

1,199 Views
Partner - Contributor III

@qliktime Nice idea, that would be quite elegant solution. By that time variables and dollar-sign expansion shall do the job quite nicely...

``=\$(vSomeSetAnalysisInVariable) [MasterItem]``

or

``={<[Field]={'Value'}, \$(vSomeSetAnalysisPartInVariable)>} [MasterItem]``
1,178 Views
Partner - Creator III

Thanks @hic, thanks for bringing us a great new feature.

I'm thinking that this will now become the new 'best practice' in writing Set Analysis.

Cheers, Barnaby.

1,153 Views
Partner - Contributor II

I've being using this new set analysis for a few weeks now, it's great.

However something funny has happened since yesterday. I had a table with several expressions in, a couple of of them using 'old' syntax and some using the new syntax e.g.

{<
[Snapshot Date]={'\$(=Max([Snapshot Date]))'}
>}
SUM([\$(vMeasure) EUR])

This was working fine until today, when the other measures (the ones using 'old' set analysis) in the table went blank. Nothing in the app had changed between yesterday and today (no new design changes, no data reloads).

After tearing our hair out we found that putting the SUM at the start of the expression resolved the problem

SUM({<
[Snapshot Date]={'\$(=Max([Snapshot Date]))'}
>}
[\$(vMeasure) EUR])

I'm really puzzled by this...have I done something wrong in the new set analysis syntax above? The solution that we've found looks weird, somewhere between the 'old' and 'new' set analysis syntax.

I feel like there's a bug that's crept in since yesterday, where the new set analysis syntax is affecting charts and tables with multiple measures.

Any help appreciated 🙂

1,098 Views
Contributor II

I am also experiencing the behaviour explained by @RoryMcHugh-Catalyst since yesterday. If you need more details in order to replicate the bug contact me.

Regards.

1,056 Views
Employee

@RoryMcHugh-Catalyst , @sergio_ot ,

Note that that as of 2022-10-18 (Engineversion=12.1505.0) the workaround below is no longer needed!

If You wrap the expression in outher parenthesises it should work as a workaround while my colleagues fix the root cause!

Like this

({<
[Snapshot Date]={'\$(=Max([Snapshot Date]))'}
>}
SUM([\$(vMeasure) EUR]))

BR

Lucke

1,035 Views
Contributor

Great article for me and my team Synodus !

1,012 Views
Specialist III

I've got the same issue. Your solution didn't make any difference

({<YearsAgo = {3} >}
SNSalesEquipAllYrs)

It was fixed by retyping what I have above

NB. I think I had a *= by mistake.

{<YearsAgo *= {3} >}
SNSalesEquipAllYrs

When I retyped I lift the * out. But not 100% sure. But hope *= will still work when required

Thanks

Further testing shows that sometimes *= works (without the ()) and sometimes it doesn't. But without *= and using instead just = it always works so far

989 Views
Contributor

Another strange behaviour I've just come across. In this case though () around the expression seemed to fix the issue.

I set up a master measure as follow. It was called SNSalesEquipAllYrs

sum({<IDSource *= {'SAGE.SOINV','SAGE.NLYum'}
,CanonType *= {KEY} ,CodeNL = {400}
,InvNum_Ref -= {"*63881*"} >} InvLineValue* DocTypeIDAdjH )

-sum({<CanonType = {KEY},GLTransType = {ActDetail, HistDetail}
, IDSource *= {'SAGE.NL','SAGE.NLYum'},CodeNL = {400}

I then used this master measure in a table measure as follows

{<YearsAgo = {0,1,2,3,4,5}>}
SNSalesEquipAllYrs

If this measure was immediately before other measures on the table that didn't use YearsAgo it filters these measures as well. But only if YearsAgo wasn't used in a measure and only if it was just before other measures that didn't use YearsAgo

After dragging to after the measure

963 Views
Partner - Contributor III

Hi @hic ,

I've read through your post and... Just wondering - is there any way to override inner Set analysis (particularly defined in master measure) with outer Set analysis?

Let's say we want to define a "default" master measure, that will by calculate sales for current year

``[Sales Master Measure] := Sum({<Year = {2022}>} Sales)``

This calculation would be valid unless the outer expression (e.g. in derived master measure) says otherwise. Let's say we want to define another master measure for Last Year Sales using the original Sales Master Measure.

``[LY Sales Master Measure] := {<Year = {2021}>} [Sales Master Measure]``

But this always returns values for 2022.

Any ideas?

P.S.: In our real-world scenario we're of course not using Year, I just tried to convert that specific case to something more generic...

870 Views
Subscribe by Topic