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 NumberCustomer NumberQuantity
12300
13550
14200
21740
23300
31470
32980
331200
34145
42870

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 NumberCustomer NumberQuantity
12300
13550
14200
21740
23300
31470
32980
34145
42870

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

2 comments:

  1. Very interesting, Nice article.

    ReplyDelete
  2. 8 Casino Hotels & Resorts - Mapyro
    Hotels 영천 출장마사지 near 부산광역 출장샵 Casino. Address, 대구광역 출장안마 Casino. 0 Casino 포천 출장마사지 Way, Atlantic City, NJ 08401 Near Casino · Fairfield Inn Casino · 속초 출장안마 Hampton Inn Casino · Bally's Atlantic City Casino.

    ReplyDelete