Qlik Exclusion Operator

Qlik's Set Expressions are one of the important topics if you plan to master Qlik Sense/Qlik View. In simple terms, we use Set Expression in aggregation functions when we want to reduce data in visualization or create a visualization independent of the current selections. While creating a Qlik dashboard, A developer should always make sure he reduces the use of the IF statement and use Set Expressions instead which increases the performance of the dashboard. I will explain to you the exclusion operator with a similar example.

So a couple of days back I had a call from a friend who had created a visualization in Qlik but was facing issues with reducing specific data from the visualization. Below is the visualization he created

Vendor Number	Customer Number	Quantity
1	2	300
1	3	550
1	4	200
2	1	740
2	3	300
3	1	470
3	2	980
3	3	1200
3	4	145
4	2	870

So from this data he wanted to exclude the row highlighted in yellow color. So he was planning to use the IF statement to reduce the data which would for sure increase the load time of the given chart. After understanding the data and use case with him we planned to use Exclusion operator in Set. The expression he created was

sum( {$<Date={"$(vMaxDate)"}>} Quantity)

Here vMaxDate is a variable with an expression

=Date( Max( Date ), 'DD/MM/YYYY')

The expression above was meant to show data for the max date available on the dashboard. Later we made changes in the expression to exclude Customer Number 3 with Vendor Number 3

sum( { $<Date={"$(vMaxDate)"}> - 1<[Vendor Number]={3},[Customer Number]={3}>} Quantity)

Vendor Number	Customer Number	Quantity
1	2	300
1	3	550
1	4	200
2	1	740
2	3	300
3	1	470
3	2	980
3	4	145
4	2	870

So the working of the set expression is as follows, $<Date={"$(vMaxDate)"}> will only show the data for the max date. - operator will exclude any expression written after it, as in our case we are excluding Customer Number 3 with Vendor Number 3 from the Max date Dataset.

For the visual learner in you, I have also created a diagram showing the working i.e from the data on the green side, data from the white side will be excluded.

There can be many similar use cases of the exclusion operator which you can be using, you can comment them below. Also there are many different set expression use cases I will be sharing with you which I have used so keep an eye for them. If you have any Qlik/Data related doubt do ping me will cover it in my upcoming post.

Till that hasta la vista