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

[stijn_verhoeff]

Hi @sergiogiogio, 

Were you able to log this as a bug with Qlik? And if so, is there a way we can show our support for it? 

We have encountered the exact same problems and I strongly agree with your previous points. This feature was released with the suggestion that it could be used to simplify Set Analysis by reducing repetition. We have adopted the feature as such and have since applied it in almost every app. 

The fact that it behaves in such an odd way, even contradicting the documentation, is simply unacceptable. Especially in large enterprise apps with Section Access applied, there is no way of knowing which field values may be excluded that certain Outer Sets may rely on, triggering this issue and resulting in incorrect data for some users, but not for others. 

Surely, whatever technical definition of 'merge' is applied here, the merging of two sets, of which one results in zero possible values, should logically never result in greater than zero values. 

@Niroopananda you mentioned improvements in the May 2024 release, could you elaborate? We are running on this release and are experiencing the exact same results (see attachments). 

Additionally, while you mention a workaround by expanding the Inner Set definition, this is of course not a solution. This bug undermines all the value that Outer Sets brought in terms of simplifying Master Measures. We may as well revert to storing expressions in variables again. Can you please confirm that this is recognized as a bug, yes or no? 

[sergiogiogio]

Hello @stijn_verhoeff, @vasilev , thanks for keeping this discussion alive, glad I am not the only one deeply troubled by this. I do not have much update unfortunately.

I initially was hopeful that the May 2024 release would have a fix, but we were upgraded last month and the outer set behavior remains the same as far as I could test. Interestingly the release notes for the May 2024 release do contain the below information, so it might be worth trying the setting mentioned (although I do not understand why a fix would need a setting for activation).

QB-22519

Qlik Sense Engine: Inner set expression issue when outer set expression is present

Fixed an issue that caused the miscalculation of inner set expressions when outer set expressions were present. To apply this fix, users must add the line "AdHocFieldIgnoreOuterSetField=1" to the settings.ini file. For information on how to edit the file, see How to modify Qlik Sense Engine settings.ini.

We have not raised an official log with qlik because I was hopeful and waiting for the upgrade. Now that you have reminded me, I will petition internally so we can official request support, but I do not know the process and how long it will take (we are a fairly large organization and I do not exactly know our channels of communcation to qlik).

Through this forum and direct messages, I did try to contact few qlik employees who wrote about this feature (including @Jennell_McIntire who wrote the initial feature announcement blog post) but to no avail.

I will definitiely update this post if the official support request route goes somewhaere. I would encourage to also try the same on your end as it might go faster.

[hadrienlp]

I logged this exact problem on November 30th, 2023 : 

00128525: Inner/Outer set expression inheritance bug when data does not exist

This is the answer I got on December 15th : 

Hi,
Our Engineering teams have confirmed that the issue you reported is a software defect and a fix has been scheduled but it will take sometime as it is a holiday break here.
Please try below workaround and let us know if it helps!
The issue is triggered when an outer and inner set expression is combined,
and the outer set expression is referencing a non-existing value.
Two different workarounds at this point are as below:
1. Put it all in a single set expression, either use inner or outer set expression.
2. Swap the inner and outer set expression with each other so that the non-existing value is in the inner set expression instead of the outer one, like so:
{<FieldA={10}>} Sum({<FieldB={"dummy"}>} Amount)
Note, second workaround still allows to have an inner and outer set expression,
but only works if the outer set expression only refers to EXISTING values.
All non-existing value references must be in the inner set expression.
Regards,
Rashmi

I responded that these workarounds were inappropriate when referencing master measures.
Because well... it kinda breaks the whole feature.

On February 7th, case was updated by Qlik with the following:

Hi,
I just wanted to update you on the progress of the request.
The bug is still under investigation and RnD is experimenting with a fix that seems to resolve the use case mentioned here.
The fix is discussed and yet to be released but I do not have the exact ETA.
Please let me know if you are okay to follow our support blog to get the news about the fix or should we keep the case open until we hear from RnD.
https://community.qlik.com/t5/Support-Updates-Blog/bg-p/qlik-support-updates-blog
Regards,
Rashmi

On April 10th, 2024 I received a new update with some bullshit workaround consisting of using if() ...
Again, not a workaround.
I did not answer until May 14th, at which point the case had been closed due to no response from my end.
I asked when the issue was finally going to get fixed, which reopened the case, and this is the last answer I had:

Hi,

This is considered as working as designed.
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.

For any further clarifications, please open new case as this case was closed 1 month back.

Thank You!
Rajani

So, in 6 months we went from "confirmed software defect" to "working as intended".
I based a LOT of standardized Apps on the master measure feature and the ability to have base expressions that can be altered through subsequent set expressions, which come "on top". It is a tremendous gain of maintenance time and simplicity in the code.

I see errors and wrong results all other the place, specially when dealing with dates (most common cases of data "missing" from datasets).

If it is working as intended, it is deeply confusing and counter-intuitive. It severely limits the master-measure-referencing feature, which would be fabulous otherwise.

I really hope that Qlik is going to revert on this decision, or address the issue differently.