Excluding values in Set Analysis

[hic]

The Set Analysis is a commonly used tool when creating static or dynamic filters inside aggregations in Qlik Sense or QlikView. But sometimes you need the opposite to selecting – you need to exclude values. How can this be done?

[hic]

In Set Analysis, it is straightforward to make selections; to define the criteria for inclusion. But it is not as straightforward to define an exclusion. But there are in fact several ways to do this.

First of all, an inclusion can be defined the following way:

This expression is equivalent to saying “Sum the Amounts where Field equals X”.

But if you want to say the opposite – “where field does not equal X” – it becomes more complicated. The relation “not equal to” is not a Set operation. However, one way to do this is to use the implicit exclusion operator:

Note the minus sign in front of the equals sign. This will create an element set based on the existing selected values, but with the value X removed.

A second way to do this is to use the unary exclusion operator:

This will return the complement set of X and use this as element set in the set expression.

In many situations the two methods return identical sets. But there are cases when they are different. In the table below you can see that it makes a difference if there already is a selection in the field.

Note the difference on the row with the C. Since the implicit operator (the 2nd expression) is based on current selection, also the value C is excluded from the calculation. This is in contrast to the unary operator (the 3rd expression) that creates a completely new element set, not based on current selection.

We can also see that both of the above expressions exclude records where the field is NULL (the line where Field has a dash only). The reason is simple: As soon as there is a selection in the field, the logical inference will exclude NULL in the same field.

So what should you do if you want to exclude X but not NULL?

The answer is simple: Use another field for your selection. Typically you should use the primary key for the table where you find the Amount.

Here you need to use the element function E(), that returns excluded values. Hence, the above Set expression says: “Select the IDs that get excluded when selecting X.”

The table confirms that we get the records we want: X is excluded but NULL is still included.

With this, I hope that you understand Set Analysis somewhat better.

HIC

Further reading related to this topic:

A Primer on Set Analysis

Why is it called Set Analysis?

Finding NULL

[#3]

great post HIC

[cbermejo]

Considero que una de las formas más claras de excluir es la siguiente: Count({<Director={'*'}-{'Mel Brooks'}>} Actor)

De esta forma, seleccionamos con el asterisco todas las opciones y restamos todas las que queremos excluir, pudiendo utilizar separadores para excluir más de una.

Además, esta manera de exclusión, no provoca errores visuales de sintaxis.

[hic]

However, {<Director={'*'}-{'Mel Brooks'}>} also excludes NULL values, and this you may not want.

HIC

[mjm]

nice explanation. This makes set analysis a lot clearer

[kkkumar82]

Hi Henric,

Is the case same for field with alternate state. Does the expression

for eg Sum({<field =>} Sales) Works or anything to be added before the field.

Thanks

Kiran Kumar

[#8]

Very helpful,

thanks for sharing!

[Anonymous]

hello in case i want to see and the excluded and the the included values in the same graph separately?

[anderseriksson]

You have an expression for the selected values?

Make a new expression: [expression for all values] - [expression for selected values]

That should give you the measure for the excluded values.

[JonSebright]

What a very useful post - found it looking for a similar one that I'd seen recently, but this gave me what I was looking for. I've just used it to show rows which have a null value in a field but based on current selections:

sum({$<[Null_Check_Field]=>*1<[ID_Field]=E({$<[Null_Check_Field]={*}>}[ID_Field])>} [Counter])

So, it's the intersection between the current selections (ignoring the field I'm checking for nulls) and records in the whole dataset (excluding those with a value in the field I'm checking for nulls).

[Akash_Dixit]

Excellent explanation. Would you mind sharing how to display all of the column data while excluding one particular column value? say, by showing the combined sales of every nation excluding China.

All I can think of is P() and E() function... below is the expression I have used but not sure what is the issue. 

sum (
{$<country = - p({$<country={'China'}>} country)>}
Sales)