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]

As usual helpful

[upaliwije]

Nice Explanation

[qlikviewwizard]

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

[arockiyaselvana]

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?

[hic]

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

[arockiyaselvana]

Understood.. Thank you for the clarification

[SreeniJD]

Hi @HIC

Very helpful.

[cleblois]

Hi,

I have one for you  

We want to analyse our products with a focus on joiners and leavers.

I found the beginning of it on the forum but now I'm stuck.

In below example, leavers for 2012 and joiners for 2013 are correct because I fixed the values in the formula.

My issue is to make it generic for this table in Sense.

current formulas:

* joiners: Count( DISTINCT {$ < DPN= P({<Year_QVW={'2013'}>} DPN)- P({<Year_QVW={'2012'} >} DPN)>} DPN)

* Leavers: Count( DISTINCT {$ < DPN= P({<Year_QVW={'2012'}>} DPN)- P({<Year_QVW={'2013'}>} DPN)>} DPN)

When I use max in the P(), it considers 2015, the max year of my selection, not the year of my column.

any idea to repalce my fixed years by years relative to my columns?

Thanks in advance,

Christophe

[anderseriksson]

An alternative when you need exclude X but include those with Null is to combine two expressions;

     Sum({$<Field=>} Amount) - Sum({$<Field={"X"} Amount)

(everything including both X & Null minus the sum with only X)

If you do not have another field suitable to use with the E() function this will solve the problem.

[ecolomer]

very helpful

thank's for sharing