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
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
Here vMaxDate is a variable with an expression
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
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
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
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)
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.
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.
Till that hasta la vista
Very interesting, Nice article.
ReplyDelete8 Casino Hotels & Resorts - Mapyro
ReplyDeleteHotels 영천 출장마사지 near 부산광역 출장샵 Casino. Address, 대구광역 출장안마 Casino. 0 Casino 포천 출장마사지 Way, Atlantic City, NJ 08401 Near Casino · Fairfield Inn Casino · 속초 출장안마 Hampton Inn Casino · Bally's Atlantic City Casino.