Group expression in Set analysis with multiple conditions

[SerSwagster]

Hi everyone,

I want to understand better how to use the group expression operators (",",  "+",  "*") in a set analysis.

This is my issue:

I have a table in which values are given by this expression:

count({<[Month]={"$(month_previous)"},[Color]={"Green"},[Weight]=P([Weight])>}distinct [ID])

and outside I have a filter for the field Color.

Selecting colors different from "Green" in the filter, I expect the table to be empty, because the selection made don't match with the Set Condition in the expression [Color]={"Green"}. Instead, the table returns me the same values, including Green Color, without considering the actual selection made by the filter.

Where is the mistake? This issue is to address to "," operator in the expression? Should I use a different group expression operator, like "*"? Or the issue depends on identifying set operators?

[marcus_sommer · Accepted solution]

Your statement ... [Color]={"Green"} ... overwrites the made selections within [Color]. To make it responsive to the selection you could change it to ... [Color] *= {"Green"} ...

- Marcus

[SerSwagster]

Yeah, it works! Can you please explain me in detail why my solution did not work and your solution works?

For example, in the opposite case, my solution worked:

count({<[Month]={"$(month_previous)"},[Color]-={"Green"},[Weight]=P([Weight])>}distinct [ID])

Filter: Green

Why this works without using "*=", and instead the previous case (color = green, filter = not green) does not work?

[marcus_sommer · Accepted solution]

For this we will need to dive a bit deeper under the hood of the data-handling in Qlik. Nevertheless the following is a quite simplified description.

Set analysis worked in general like a selection within the UI. This selection set a boolean value of true or false to the internal state-tables. The state-tables are a kind of a parallel-table to the system-tables. The system-tables store the distinct field-values of a field - each field has an own system-table with the values and a binary pointer to the data-tables.

Everything what is shown in Qlik is an aggregation which has a dimensional context. To get this context Qlik collects all possible values from the system-tables which are defined through the true/false-flagging from the state-tables. Whereby the state-tables have not mandatory a single-state-column else there could be multiple ones because there could be n alternate states be defined and also a lot of set analysis.

If there are now multiple states for a dimension-value possible Qlik need to evaluate the states with various priority/ordering/matching rules. In the end the final state has only one value - true or false.

By applying [Color]={'Green'} within the set analysis Qlik checks the states and finds two values - true/false from your selection and true from the set analysis. The set analysis has a higher priority as the pure selection and therefore the selection-state is ignored and the dimension-value of 'Green' by Color is considered for the calculation.

Adding any identifier/modifier/operator to a set analysis statement impact the weighting of the now multiple parameters. By using [Color] *= {'Green'} both state-values are equally weighted which means all of them must be true. By applying [Color] -= {'Green'} it looked similar to [Color] = {'Green'} and that the set analysis statement get a higher priority unless it removed the value of 'Green' but there is more because other values from field [Color] are further available and selecting them will have an appropriate impact.

From an UI point of view you could imagine the impact of [Color] -= {'Green'} as if you hadn't loaded 'Green' into the field Color - means if you look on the list-box it wouldn't be there - but of course the other values are selectable. 

Although the laborious explanation I hope it will be helpful for you and others.

- Marcus

[SerSwagster]

Thanks a lot Marcus!!!