Qlik Community

Ask a Question

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
Henric_Cronström

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:

 

Formula0.png

 

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:

 

Formula1.png

 

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:

 

Formula2.png

 

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.

 

Image5.png

 

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.

 

Formula3.png

 

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

 

Image7.png

 

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

39 Comments
Not applicable

As usual helpful

20,401 Views
upaliwije
Creator II
Creator II

Nice Explanation

0 Likes
20,401 Views
qlikviewwizard
Master II
Master II

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

0 Likes
20,401 Views
arockiyaselvana
Partner
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?Exclude.PNG

0 Likes
20,401 Views
Henric_Cronström

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
arockiyaselvana
Partner
Partner

Understood.. Thank you for the clarification

0 Likes
20,401 Views