# Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
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

20,401 Views
Creator II

Nice Explanation

20,401 Views
Master II

Beautiful explanation. Thank you for the such a blog post.

20,401 Views
Partner

Thanks for the content.

When i Try the same in Qlik Sense, below is what i am getting in Column 3.

So any idea why is this different?

20,401 Views
Employee

There is no difference in Set Analysis between QlikView and Qlik Sense.

Instead, what you have is not a NULL. You have a blank, and as such it is selectable, You can see this on the fact that it appears on its own row in the filter box.

See more on NULL handling in QlikView

HIC

20,401 Views
Partner

Understood.. Thank you for the clarification

20,401 Views