Bug report: Outer set expression is not inherited if 1) the outset set expression selects an inexistant value, 2) there is an inner set expression

[sergiogiogio]

I would like to report what I believe is a bug in inner and outer set expressions feature (May 2023 patch 1), not sure if this forum can be a suitable location.

Given the the script, 

Fruits:
LOAD * Inline [
Type,Color,Quantity
apple,green,1
apple,green,1
apple,green,1
orange,orange,1
orange,orange,1
];

Consider the below formulae outputs, where we select apples (which exist in the data set) and kiwis (which do not exist):

Sum({<Type={'apple'}, Color={'green'}>} Quantity)	3	OK
{<Type={'apple'}>} Sum({<Color={'green'}>} Quantity)	3	OK
{<Type={'apple'}, Color={'green'}>} Sum(Quantity)	3	OK
Sum({<Type={'kiwi'}, Color={'green'}>} Quantity)	0	OK
{<Type={'kiwi'}>} Sum( {<Color={'green'}>} Quantity)	3	WRONG!! The outer set analysis is not inherited!
{<Type={'kiwi'}, Color={'green'}>} Sum(Quantity)	0	OK
Sum({<Type={'orange'}, Color={'green'}>} Quantity)	0	OK
{<Type={'orange'}>} Sum( {<Color={'green'}>} Quantity)	0	OK
{<Type={'orange'}, Color={'green'}>} Sum(Quantity)	0	OK
Sum({<Type={'kiwi'}>} Quantity)	0	OK
{<Type={'kiwi'}>} Sum( Quantity)	0	OK

As per the results above: outer set expression is not inherited if

1. the outset set expression selects an inexistant value,
2. there is an inner set expression

Could someone confirm this is indeed a bug and whether it has already been fixed in the recent version? Thanks!

[sergiogiogio]

The same issue has been reported at least 3 other times since Aug 2023 without resolution or explanations.

1. https://community.qlik.com/t5/New-to-Qlik-Analytics/Outer-SetAnalysis-modifier-ignored/td-p/2108889
2. https://community.qlik.com/t5/QlikView-App-Dev/Is-this-a-bug-with-the-new-outer-set-analysis-syntax/...
3. https://community.qlik.com/t5/App-Development/Problem-with-set-analysis-outer-inner-set-expression/t...

At this moment, I understand the issue is triggered when:

• a merge of set expressions is performed (inner or outer does not matter)
• an outer set expression "exclusively" resolves into an empty set. In other words, that outer set expression taken in isolation would never return anything anyway.

under these 2 conditions, the merge operation will simply ignore the outer set.

If no merge is needed, the result will be correct, so it really seems to be a bug with the merge operation.

I cannot stress enough how important this issue is - it might lead to incorrect results completely silently and very difficult to debug.

The longer this issue is delayed, the more difficult it will become to resolve, as changing the behavior over time will disrupt an increasing number of applications.

Tagging @Jennell_McIntire who wrote the introduction blog post for help in escalating the issue. 

[marcus_sommer]

IMO it's not a bug else behaved as designed. Essentially is that inner- and outer-statements are not regarded as a single statement else it are independent statements which are performed in an order.

You could imagine your "wrong" example as two selections. The first selects 'kiwi' and then the selection of 'green' happens - which deselects 'kiwi' again.

The outer statements and especially the combination of inner- and outer-statements are not a simplification or a replacement for a normal set statement else it is an advanced feature for special scenarios.

[sergiogiogio]

Thanks @marcus_sommer for your reply.

---

You could imagine your "wrong" example as two selections. The first selects 'kiwi' and then the selection of 'green' happens - which deselects 'kiwi' again.

---

'kiwi' does not exist in the dataset. Your scenario matches the test below where 'orange' exists in the data. The result of this test is 0, which is correct in my opinion. If we go by your proposed explanation, it should show 3. This would hopefully convince you that there is something deeper to investigate here.

{<Type={'orange'}>} Sum( {<Color={'green'}>} Quantity)

---

The outer statements and especially the combination of inner- and outer-statements are not a simplification or a replacement for a normal set statement else it is an advanced feature for special scenarios.

---

Can you please provide references for the special scenarios it should only be used for? The documentation simply says "Use a set expression outside the aggregation function if you have expressions with multiple aggregations and want to avoid writing the same set expression in every aggregation function."

One important use case is useing it for measures as explained in the documentation? 

{<Year={2021}>} [Master Measure]

• If [Master Measure] does not contain an inner expression, the above expression will always return the expected result.
• If [Master Measure] does contain an inner expression, and if the year 2021 exists in the dataset, the above expression will always return the expected result.
• If [Master Measure] does contain an inner expression, and if the year 2021 does not exist in the dataset, the year selection will be ignored.

You might not have control over the way the [Master Measure] are defined. That definition might also change overtime. If there is no consistency in the behavior (as is illustrated above), using outer expression for master measures is then not advisable, which then contradicts the documentation.

[marcus_sommer]

I have no examples for it because I don't use these features in our productive environment and I doubt that I would implement them as a general approach within a business release in the future.

Yes, the documentation gives the impression that it are must-use features but IMO in goes in the wrong direction of implementing the essential business logic within the UI and the data-model isn't really important anymore and is only a kind of data-lake and the smart developer just picked the wanted data.

In my experience it's much harder and needs much more efforts to develop the logic within the UI instead of the data-model. Ideally the most calculations are possible with simple sum() and count() without much set analysis and the users just select what they want to see: Let the User Select - Qlik Community - 1463978. Each kind of own usability beyond the green-white-grey selection-logic will increase the complexity and needed efforts - therefore be careful with it.

[Niroopananda]

Hi @sergiogiogio,

This scenario occurs in these conditions:

• an outer set-expression results in empty set (with or without selections)
• an inner set-expression is defined

Then the calculation ignores the intersection between 2 set-expressions and uses the inner one only.

As a straightforward workaround, an outer set-expression can be easily merged into the inner set-expression, then the calculation will be correct.

Only when SetExpr1 (the outer) is evaluated with an empty result, it is not merged with SetExpr2 (the inner), instead, only the SetExpr2 is considered.

{SetExpr1} AggrFunc ({SetExpr2} expr)

When multiple set-expressions exist (no matter outer or inner), engine evaluates the selections for each and tries to merge the results. if in any step the result is empty, engine considers that the whole dataset is selected then continue with the subsequent evaluations. that is how engine search works. following expressions always return the same result

{<condition1>}AggrFunc ({<condition12>} expr)
AggrFunc({<condition1>} {<condition12>} expr)

While multiple conditions in one set-expressions are intersected, for instance this statement will return empty set while the first or the 2nd condition return empty.

AggrFunc({<condition1, condition12>} expr) 

We have some improvements included in May 2024 release. Suggest to upgrade to May 2024 version. 

[sergiogiogio]

Hello @Niroopananda, thanks a lot for the analysis!

The explanation is useful but I believe the rules are actually far more complex

1- The below expression returns 3 (kiwi is ignored) which means that the issue is also triggered by merging outer set expressions, there is no need to have an inner set expressions. 

{<Type={'kiwi'}>} {<Color={'green'}>} Sum(Quantity)

This is why my take is that the issue is with the merge function (no merge --> no problem)

2- You mentioned  that the below 2 expressions always return the same result: 

{<condition1>}AggrFunc ({<condition12>} expr)
AggrFunc({<condition1>} {<condition12>} expr)

The below results show this is not necessarily the case 

Sum( {<Type={'orange'}>} {<Color={'green'}>} Quantity)	3
{<Type={'orange'}>} Sum( {<Color={'green'}>} Quantity)	0

3- the parenthesis play an unexpected role too

Sum( {<Type={'orange'}>} {<Color={'green'}>} Quantity)	3
Sum( {<Type={'orange'}>} ( {<Color={'green'}>} Quantity ))	2

the initial issue I reported can be corrected using parenthesis

{<Type={'kiwi'}>} Sum( {<Color={'green'}>} Quantity)	3
{<Type={'kiwi'}>} Sum(  ( {<Color={'green'}>} Quantity ) )	0

I would be interested to make sense of this confusing behaviors. Please note that these behaviors shuold be somehow described in an understnadable way in the documentation. If they can't be documened then there is a strong argument they are in fact bugs and should be corrected. 

You mentioned that there are improvements in May 2024 release, but the fundamental question in my opinion is whether this is a bug or not (is this the desired behavior or not). 

[Firefly_cam]

So according to your examples the following formula is a bug?

Sum({<Type={'kiwi'}> + <Color={'green'}>} Quantity)

Agree with @marcus_sommer , seems that it's behaved as designed and merged with a "+" - union, not "*" - intersection

[marcus_sommer]

I have the impression you are trying to use the set analysis for not suitable scenarios.

Essential is the understanding that it is a way to transfer a selection state into an expression respectively with the newer inner + outer set statements against a calculation. Not more.

This means you could use a reversed approach to comprehend the logic and to check if it's applicable within your intended use-case by removing the entire set analysis and just selecting the wanted view from the outside with the normal list-boxes / filter-panes. If you are able to select the existing field-values within the n fields one after another you will be able to transfer it to a set analysis.

Of course there are some advanced possibilities in chaining n set statements with n operators in nested hierarchies/orders and/or against/within virtual data-sets by wrapping the calculations with aggr(). Be careful with it because only because it's technically possible it must not be sensible to implement - neither from the usability nor from the complexity and the needed efforts to develop and maintain it. Especially if you have no full control of the data-model and data-set - there is measurement implemented to check if fields and/or field-values directly exists respectively in a certain combination. It are just selections.

[sergiogiogio]

Hello @Firefly_cam, many thanks for taking the time to reproducing the use case above!

I indeed would like to understand how the merge operation works, and more importantly how it is expected to work. While your example does suggest "+", the example below suggests rather a "*" (intersection). So what exactly is it doing?