Starting With Set Analysis in Qlik

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 release of Qlik Sense, that has all changed. The inclusion of the set analysis builder allows users to insert set analysis direct into calculations

To do this, open the 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.

Next, create a calculation/KPI and click the expression editor

Choose the fields and the type of aggregation from the right hand side and insert

Insert the set analysis after the first bracket of the calculation as per the below

As you can see, 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.

https://community.qlik.com/t5/QlikView-Documents/Romancing-with-Set-Analysis/ta-p/1485889

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.

Leave a Reply

Your email address will not be published. Required fields are marked *