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]

---

@marcus_sommer wrote:

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.

---

You're suggesting that Set Analysis should equate to selection, which seems like a logical approach, but the Qlik engine does not adhere to this principle in practice. For instance, consider the following formula that returns 0, despite the absence of 'kiwi' in the dataset. According to your theory, if 'kiwi' cannot be selected because it doesn't exist, then this selection should not influence the outcome, and the formula should return 3.

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

Based on this qlik community thread, I was inclided to believe that the first (n-1) Set Analysis in an expression functioned as successive selections, with only the final Set Analysis acting as an intersection. However the following formula is a counter example and returns 3.

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

 So I am personally back to square one. Fundamentally I only to understand what the merge operation is supposed to do.

---

@marcus_sommer wrote:

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.

---

Suppose you have a P&L segmented by year and entity, and you need to report the P&L for NY in 2021. Using outer expressions (Option 2) simplifies and streamlines the process compared to inner expressions (Option 1). This is because outer expressions allow for the easy reuse of definitions, making them more maintainable.

	Option1	Option2
PL	(No PL definition)	SUM(Revenue) - Sum(Expense)
NY PL	SUM({<Entity={'NY'}>} Revenue) - Sum({<Entity={'NY'}>} Expense)	{<Entity={'NY'}>} PL
NY 2021 PL	SUM({<Year={'2021'}, Entity={'NY'}>} Revenue) - Sum({<Year={'2021'}, Entity={'NY'}>} Expense)	{<Year={'2021'}>} [NY PL]

Now, consider the scenario where you need to incorporate interest expense into the P&L. In Option 1, this change would require updates to multiple formulas. With Option 2, you only need to adjust the root formula. This central update automatically propagates throughout the related expressions.

---

@marcus_sommer wrote:

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.

---

Reusability/DRY (Don't Repeat Yourself) principles are best practices in software development and I believe it is common for developers not to have full control of the underlying data. As a qlik developer you would want to leverage standard definitions to build your logic, and you would expect your formulae to continue working even if the underlying definition is updated.  

[marcus_sommer]

I would try very hard to avoid each set analysis within a P&L and just using an expression like: sum(Value) for everything and each essential part of the logic is resolved within the dimensional layer created in the data-model.

Just selecting the wanted (nested and maybe overlapping) hierarchy-level and periods and showing/hiding the hierarchy-level within the appropriate objects. Usually it's much harder to implement the needed logic within the UI as doing the work within the data-model.

[sergiogiogio]

Thanks @marcus_sommer , if I understand you correctly, your data model would look like that:

Year	Entity	Type	PL
2021	NY	Revenue	+XXXX
2021	NY	Expense	-YYYY

Then PL could simply be computed as

SUM(PL)

For calculating 2021 PL, you'd use

SUM( {<Year={'2021'}>} PL)

This is done using only inner set analysis, without the need for outer set analysis.

This works for PL but imagine you now need to show Margin, the formula is more complex:

SUM(PL)/SUM({<Type={'Revenue'}>} PL)

It would be wise at this stage to put it into a master measure ([Margin]) to prevent any error from the data model users. At this point outer set analysis becomes important. 2021 Margin can simply be expressed as

{<Year={'2021'}>} [Margin]

instead of the error prone

SUM({<Year={'2021'}>} PL)/SUM( {<Year={'2021', Type={'Revenue'}>} PL)

I completely agree with your point that we should generally avoid Set Analysis and rely on Selections but it is not always possible. For example your users might want to compare every year's Revenue to the 2021 Revenue used as a reference year. There, you need to somehow hardcode 2021 using Set Analysis. The alternative is to ask users to export and do this comparison in excel, whcih undermines qlik capabilities.

More fundamentally, everyone might have different requirements or even preferences that will prompt them to use different features. The outer set analysis feature has been released - the purpose of this thread is to uderstand how it is expected to work.

• If qlik agrees the behavior to be a bug, then it should be fixed as quickly as possible to minimize the number of users impacted during the next upgrade (users who are relying on the incorrect current behavior).
• If qlik considers this is the expected behavior, then the documentation should be updated to explain precisely the current behavior. This would surely turn away most users from using the feature, but would at least prevent them from wasting a frustrating amount of time troubleshooting this unintuitive behavior.

[marcus_sommer]

Yes, something in this way was meant. Unfortunately (respectively luckily) there is no general approach how to fulfill all requirements with a single solution which is at the same time simple, without much efforts during the development/maintaining and not especially error-prone.

Personally I regard the shown set analysis as quite simple and no basic-developer should have serious challenges with it. If transferring any of these expressions into a master-measure with/without the use of outer set statements is a big and desirable simplification ... I'm not sure. In some way it's the opposite because (parts of) the content will be hidden and are preventing the understanding.

Nevertheless if expressions/logic should be outsourced and possible redundancy minimized the use of parametrized variables might be an alternatively respectively more suitable. Each combining and/or nesting of logic will increase the complexity - regardless if it are inner + outer set statements and/or with variables and/or master-measures and/or any combination of them. 

Beside this I could imagine to provide the users some extra selections to control the wanted views. This may be the base-year which might be considered with something like:

sum({< Year = p(BaseYear) >} PL) or sum({< Year = {"$(=max(BaseYear))"} >} PL)

and such logic is also applicable to other fields and might be make the way and direction of the rate-calculations selectable.

It's not really simplifying the matter but even the rate-calculations might be replaced with sum() approaches. This could be reached by using a logarithm-approach. Here a short example of the idea behind it:

Sales: load *, 'S' as Q, log(W) as V inline [K, W
V, 95
C, 80];

Budget: load *, 'B' as Q, log(W) * -1 as V inline [K, W
V, 105
C, 95];

and then rate from Sales to Budget could be calculated with:

which means a rate-calculation doesn't required mandatory a division.

[sergiogiogio]

It seems that this community thread is not progressing any longer, which I can understand since this is primarily for user collaboration, not an direct line to qlik support. Does any one know of any official way to reach out to qlik and report this bug?

[marcus_sommer]

Here are a lot of ways described How to contact Qlik Support - Qlik Community - 1837529.

[vasilev]

Hello @sergiogiogio,

I have the same problem and I implemented your workaround idea with the parenthesis. It works. Thank you very much! That is definitely a bug. It's a pity that the Qlik people don't understand the real problem and refuse to report this bug to the R&D.

BR,

Rumen