Set Analysis in QlikView – simplified!

One of the best practices I follow while preparing any report / dashboard is to provide a lot of context. This typically makes a dashboard lot more meaningful and action oriented. For example, if you just provide number of units sold by a product line in a month, it is good information, but it is not-actionable. If you add comparison against same month last year, last month or average of relevant product lines in this month, you have added context to the number. The business user can take more meaningful actions out of this report / dashboard.

QlikView has feature called SET ANALYSIS that provides us a way to add this context. Set analysis predefines the SET OF DATA that our charts / tables use. So, using a Set Expression, we can tell our object (chart / table) to display values corresponding to various sets of data (e.g. a pre-defined time-period, geographic region, product lines etc.). All of the examples, I mentioned above as part of adding context can be accomplished using Set Analysis in Qlikview.

Most of the QlikView Professionals think that SET ANALYSIS is a complex feature. Through this post, I am trying to change their conviction towards it.

[stextbox id = “section”] What is SET ANALYSIS ? [/stextbox]

Set Analysis can be understood by a simple analogy of how Qlikview works. We make selections on certain variables and the changes reflect in the entire application. This happens because through our selection, we have created a set of data which we want to use. In a similar fashion, using Set Analysis feature, we can pre-define the data to be displayed in our charts.

Some features and characteristics for Set analysis are:

• It is used to create different selection compared to the current application selections
• Must be used in aggregation function (Sum, Count….).
• Expression always begins and ends with curly brackets { }

[stextbox id = “section”] Example dataset:[/stextbox]

[stextbox id = “section”] SET ANALYSIS syntax broken down into three components: [/stextbox]

[stextbox id = “section”]  Identifiers:  [/stextbox]

Examples:–

In below example, Current year selection is 2012 and previous selection was 2013.

[stextbox id = “section”] Operators : [/stextbox]

• It  works on set identifiers

Examples:–

In below example, I have created a bookmark “BOOKMARK_1” for company selection A, B and C.

[stextbox id = “section”]  Modifiers:   [/stextbox]

• Modifiers are always in angle brackets <>.
• It consists multiple fields and all fields have selection criteria.
• Condition of fields within modifiers bypass the current selection criteria.

[stextbox id = “section”]  Dollar Sign Expansion:  [/stextbox]

If we want to compare current year sale with previous year, previous year sales should reflect values in relation to current selection of year. For example if current selection of year is 2012, previous year should be 2011 and for current selection of year 2013, previous year is 2012.

 “=Sum ({$<Year = {$ (=Max (Year)-1)} >} Sale) “

 Above expression always returns sale for previous year. Here $ sign (Font color red) is used to evaluate the value for previous year. $ sign is used to evaluate expression and to use variables in set modifiers. If we have variable that holds last year value (vLASTYEAR) then expression can be written as:

“=Sum ({$vLASTYEAR)} >} Sale) “

[stextbox id = "section"]  Indirect SET ANALYSIS: Function P() and E() [/stextbox]

 Let us take a scenario, where we want to show current sales of the companies who had sales last year.

Expression should be similar like:

=sum({$<Year={$(=Max(Year))},Company_Name={Companies who had sales last year}> } Sale)

First we have to identify companies who had sales last year. To fix this problem, we will use function P() that is used to identify values within a field and function E() that exclude values within a field.

Finally, we have expression:

=sum({<Year={$(=Max(Year))},Company_Name=P({<Year={$(=Max(Year)-1)}>}Company_Name)>}Sale)

This post was an example where we have brought out methods to use SET ANALYSIS in Qlikview. Have you used this feature before? If yes, did you find it useful? Do you have more nifty tricks to make Set Analysis more interesting? If not, do you think this article will enable you to use Set Analysis in your next dashboard?

Do let me know your thoughts on using this feature in QlikView.

If you like what you just read & want to continue your analytics learning, subscribe to our emails or like our facebook page.

Sunil Ray

Sunil Ray is Chief Content Officer at Analytics Vidhya, India's largest Analytics community. I am deeply passionate about understanding and explaining concepts from first principles. In my current role, I am responsible for creating top notch content for Analytics Vidhya including its courses, conferences, blogs and Competitions.

I thrive in fast paced environment and love building and scaling products which unleash huge value for customers using data and technology. Over the last 6 years, I have built the content team and created multiple data products at Analytics Vidhya.

Prior to Analytics Vidhya, I have 7+ years of experience working with several insurance companies like Max Life, Max Bupa, Birla Sun Life & Aviva Life Insurance in different data roles.

Industry exposure: Insurance, and EdTech

Major capabilities: Content Development, Product Management, Analytics, Growth Strategy.