Disclaimer: This is a compilation of a bit of my own knowledge combined with a lot of knowledge shared by members of the QlikCommunity through the forums. Feel free to use this information as pleased, just give credit when appropriate.
Update 2012/12/13: Barry Harmsen and I have just released our book QlikView 11 for Developers. While it is not free, it does offer a cost-friendly, guided way to learn QlikView and contains an entire chapter about Set Analysis. More information can be found by clicking here.
Introduction.
This is the First Part of a Series called ‘The Magic of Set Analysis’ which is intended to serve as a guide for those newcomers to the subject that want to take full control of their chart expressions.
I will, over the Series, cover topics like when to use it, why should you use it, what is the correct syntax, and I will provide common examples and several tips & tricks for maximizing performance as well.
When Should You Use It?
Set Analysis is a great feature in QlikView that lets you take control over what your charts display. You can compare it to the selections made using the UI. You know how selections work, right? Whatever is selected affects the whole application and charts only display information associated with the SET of Data corresponding to those selections. Plain and simple. So, that is somehow what Set Analysis does: It “restricts” or “predefines” the SET OF DATA that your charts use. So, using a Set Expression, you can tell your chart to display values corresponding, to ‘Region A’ and ‘Region B’, or exclude ‘Region C’, even if it is part of the selections. It can even expand selections made by the user to show, for example, results of the previous year (user selects year 2010, and the table displays data for 2009 in one column and 2010 in the other).
That is the whole concept. It is easy, right? Think again. It is, sometimes, not as simple to tell your chart “show me the numbers of the previous year/month/period” as it would be using selections. You need to specify it using an expression with the correct syntax.
These are some situations when you might want to use Set Analysis:
- To compare results for two periods of time (Point in Time Reporting), based on the same selections.
- To restrict or exclude certain value in a field from the calculation.
- To create a cumulative sum or YTD result, even if the user selects only one month.
- To ignore selections in a certain field.
- To ignore ALL selections.
- To use the Set of Data returned by a Bookmark.
- To replace IF expressions that make use of lots of hardware resources.
The Magic of Set Analysis will let you do a lot of nice things with your data set. I will help you understand how on the coming posts.
Update 2019/04/25: If you found this useful, you may want to take a look at the Set Analysis Cheat Sheet and invaluable resource for Qlik Developers.
Qlik Set Analysis Cheat Sheet. By Miguel Ángel García
Leave A Comment