Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
Former Employee

## Excluding values in Set Analysis

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?

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

Not applicable

great post HIC

1,282 Views
Partner - Contributor III

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.

1,282 Views
Former Employee

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

HIC

1,282 Views
Employee

nice explanation. This makes set analysis a lot clearer

1,282 Views
Specialist III

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

1,282 Views
Not applicable

thanks for sharing!

1,282 Views
Anonymous
Not applicable

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

982 Views
Partner - Specialist

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.

982 Views
Contributor

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).

909 Views
Contributor II

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)

414 Views
Subscribe by Topic