I remember my first days working with QlikView with fond memories, however in the first few days the thing I struggled with the most was the set analysis. All the brackets!
For a lot of new developers, set analysis can be daunting, but it shouldn’t be. Set analysis is extremely powerful and can be very quick to learn.
With the November 2018 release of Qlik Sense, that all changed. The inclusion of the set analysis builder allows users to insert set analysis direct into calculations
To do this:
- Open a dashboard and make the selection that you want included in set analysis. Something like a selection on a flag field or years that you want calculations limited too.
2. Next, create a calculation/KPI and click the expression editor
3. Choose the fields and the type of aggregation from the right hand side and insert
4. Insert the set analysis after the first bracket of the calculation
You now have a fully working piece of set analysis.
Obviously this didn’t exist when I started using set analysis, therefore I had to learn from somewhere. The best article I have come across for set analysis in Qlik is by Rajesh Vaswani and is called Romancing with Set Analysis. Below is a link to the community post.
The quick summary of this article is to start with a basic calculation
Sum(Sales)
Add your set analysis brackets. Brackets are always in pairs and if you click a bracket in Qlik Sense, it will turn yellow and show you where the starting and closing brackets are.
First a set of curly brackets
Sum({} Sales)
Then step inside these and add a set of angular brackets
Sum({<>} Sales)
Now add the parameters you wish to limit the calculation on. The first part of this is the field in your data model you wish to use. In this case Year. The second part is the value inside that field. This value is placed in another set of curly brackets.
Sum({<Year = {‘2019’}>} Sales)
You have now limited this calculation on just Sales for 2019. Additional fields can be added and nested in if needed. This is done by simply adding a comma and repeating the field and value section from above.
Sum({<Year = {‘2019’}, Month = {‘April’}>} Sales)
There are additional things you can do with set analysis, such as using variables and using identifiers but I shall leave that for another day.
