LivingQlik Roots: The Ultimate QlikView Set Analysis Reference

QlikView Set Analysis Ultimate Reference Living Qlik Featured Image

QlikView Set Analysis – A Scary Subject

Set Analysis is a scary subject. I would surely claim it is the most difficult part of developing your QlikView front-end.

I have waited to throw my hat in the ring regarding set analysis. This is mostly because I didn’t feel I had much to add, but also because Set Analysis in QlikView is a formidable subject.

But the time has come for LivingQlik to cover Set Analysis. Firstly, no self-respecting blog would be complete without tackling the subject. Secondly, I feel that there is room for a compete reference presented in a clear voice. Can LivingQlik be that voice? Maybe?

This is the ultimate cheat sheet for Set Analysis in QlikView. It includes the syntax definitions and options, many examples and tons of tips and tricks to make the most out of this complex topic.

One thing I tell students is to not beat themselves up if they don’t absorb everything there is to know about Set Analysis in one sitting. It is a difficult subject for all skill levels, from newbie to experienced. I know I still run into situations that make me scratch my head and I have been doing this for a while.

What is Set Analysis?

The purpose of Set Analysis is simple.

Set Analysis enables a Qlik aggregation expression to be calculated with a set of selections that differ from the user’s selected values.

When we use an expression with set analysis inside a chart, the chart will be calculated based on the data set within the expression rather than the green selected values and white associated values that would normally filter the chart.

This line chart shows Quebec sales even when Quebec is not selected

Comparing points-in-time would be difficult without set analysis.

Here we look at a month vs the same month last year.

Looking at Sub-Categories that begin with the word “Office”

How Does Set Analysis Work?

Admittedly, the syntax for QlikView Set Analysis is not easy. Here, I will lay it out as plainly as possible. Where there are several options, I have attempted to highlight the more common use-cases.

Set Analysis is always enclosed in curly braces and must exist within an aggregation like sum(), avg(), max(), min(), or only(), for example. In fact, Set Analysis is the only place where curly brackets are utilized in Qlik syntax.

Sum up the sales for all data excluding the current selections limited to the Quebec Region.

The syntax for Set Analysis is broken out kind of like grammar rules break out a sentence. Some say there are 3 parts but I detail 4 (Element List is really part of the modifier but this is where much of the “action” takes place):

  • Identifier – the selection state established for your set
  • Operator – Method for combining two or more identifier-modifier sets together
  • Modifier – Assigns specific field values to your set expression
  • Element List – Within the modifier, the specific selections assigned to your modifier field

 

Identifier – the selection state established for your set

An identifier is an optional prefix that determines the starting selection state of our set. Identifiers get applied to the modifiers they precede.

The default identifier is the dollar-sign {$} which tells the expression to respect and apply the user’s current selections in the default state before evaluating the set. Because developers are usually using the default state of the data model, developers often leave the dollar sign out since we are already in the default state. If your object or sheet is responding to another state, and you actually want the expression to reference the default state, then the dollar-sign would be needed. Omitting the identifier just means we are responding to the state of the object or sheet following normal inheritance rules.

 

 $ 
 Respects user selections in the default state of the object 

 1 
 All the data (ignores all user selections) 

BookmarkID
The selections that would be invoked by a bookmark

Alternate State ID
The selections from an alternate document state

$1
Previous Selection (like hitting back) (ignores all user selections)

$2
2nd previous selection

$_1 
Forward Selection

$_2
2nd forward Selection

0
Empty Set

 

Average of sales for all data in the application (ignore all user selections).

 

When working in the default state we often omit the $ as an operator as it is redundant. But be careful about omitting the identifier when working with other states.

Operator – Method for combining two or more identifier-modifier sets together

Operators are used to join two identifier-modifier sets. Operators can also be used inside a modifier element (more on this in the Element List section). There are only 4 variations.

Operator
Definition

+
unions two sets together

exclusion of second set from the first

 * 
 intersection of two sets 

/
symmetric difference (values that belong to either but not both sets)

 

Identifiers and operators can be used to create some very interesting sets that might (or might not) have value to you. I find that I use the intersection operator more often than any of the others, but in all reality, the sets I require generally do not require an operator.

Sum of profit for Region Ontario and Product Category Furniture but also including profit for Region Nunavut and Product Category Technology.

Sum of profit for all the data in the app excluding the current user selections.

Sum of profit for last year but only for the period less than today one year ago. This is profit LYTD.

Sum of profit for Chris and Erin or for Year 2017, but not for Chris and Erin in 2017.

Modifiers – Assigns specific field values to your set expression

A modifier gives you a way to assign specific values to fields when determining your set. The modifier is always enclosed by angle brackets: <>. Here you will list field names, each followed by an equal sign and then an element list. The modifier is where things start to get interesting.

Sum of sales for Quebec Region.

Within a modifier you can:

…assign values to multiple fields separated by commas:

Sum of sales for Ship Mode Express Air in Region Prarie.

…assign a field to the same field in another state

Sum of sales transferring the Manager selections in Group1 to the default state.

Element Lists – The specific selections assigned to your modifier field

The portion of the modifier after the equal sign is called the element list. Values listed or evaluated here become specific selections for your set evaluation. The element list is surrounded by curly braces and should not be confused as an inside or nested set. We can do many awesome things with the element set and it is often where I spend the most time when designing Set Analysis expressions.

Within an element list you can:

…assign more than one value to a field separated by commas

Sum of sales for Ship Modes Express Air and Regular Air.

…utilize wildcard searches

Sum of sales where product name begins with “Avery”.

For element values, it is best to use single quotes to identify a string although if the text does not contain spaces or other special characters, you can get away with not using them. When expecting numeric values, no quotes are necessary. Be advised that date fields can be tricky and will conditionally require quotes based on the evaluation and format of the field.

A field used inside a modifier overwrites the user’s selection for that field. Therefore we can do things like “ignore the user selections for this field”. But there is some nuance in how you should approach this. For example, the two following examples will treat the Manager field slightly differently and could result in different calculations:

Sum of sales respecting all user selections and selecting all possible (non-null) values for the field SalesPerson.

 

Sum of sales respecting all user selections ignoring user selections in the SalesPerson field.

Searching for the Element List Values

One way of developing your element list is to search for it. Enclose your advanced QlikView search syntax inside double quotes within your element list. This is especially helpful to limit your set to a date range for example.

Sum of sales for the last 30 days from the maximum current date selection.

Here we have closed the range.

Sum of sales for the last 30 days measured from yesterday backwards

You can define a calculation to search for values.

Avg of sales for the dimension value that ranks number 1

Or even assign a field to another field.

Sum of sales for Date_Field where the Date_Field is the same as the Ship Date (same day shipping)

P() and E() – Special Elements

The P() and E() special elements return the element set that is possible or excluded (respectively) from a field or expression. I don’t have requirements that call for the possible or excluded functions very often.

Sum of sales respecting current selections for the Managers that have ever sold Product Sub-Category Labels.

And an example with e():

Sum of sales respecting current selections for the Product Names that were not associated (not sold) last year.

Passing States into the Modifier

Another interesting technique involves alternate states you might have in your document. We have already seen the possibility of using these states as operators. But we can also use them in the modifier to pass the selections in a field from one state to another.

Sum sales in the current inhereted state passing in the selected values from the Group1 state.

Dynamic Elements – Dollar-Sign Expansion

Dollar-sign expansion is a frequently used feature of Set Analysis. Think of the example where you want to display sales for this year-to-date. I could just type whatever the current year is into the modifier element list, but then I would have to come back to this expression on January 1st and change the year.

Thankfully, you will not need to hard-code a year and then change to the next year every January. Using the concept of Dollar-Sign Expansion, you can dynamically assign the value to the modifier element.

Syntax inside the dollar-sign expansion will be evaluated once for the entire chart before the rest of the expression is calculated. This is usually great. But because of this, you cannot leverage set analysis to do row-by-row evaluations, expecting the set to change for each dimensional value. For those occasions, use an if statement.

Here are some examples of some point-in-time sets using dollar-sign-expansion. These are examples of how I do it, but there are many ways to “skin the cat” and different approaches have different advantages.

Sum of sales for the maximum year in the data.

Sum of sales for the maximum month in the data vs. the same month in the prior year.

Sum of sales for maximum year-to-date vs. the prior year-to-date.

Operators within Modifiers

The use of operators is not limited to the beginning of the set. You can also utilize modifiers within a modifier either as an assignment operator or inside an element list (You can ignore the syntax checker here)

Sum of profit for the currently selected Product Sub-Categories adding the selections Envelopes and Labels to this.

You can also assign the operator before the element list. This has the exact same result as the above example.

Sum of profit for the currently selected Product Sub-Categories adding the selections Envelopes and Labels to this list.

A word of caution here. If you need to use multiple operators within the modifier, put them all in the element list rather than utilizing both the element list and the assignment operator before the list. Using the second example below will not exclude Paper.

Another great example is the use of asterisk and equals. This has the effect of considering the user’s selection from the modifier field before calculating the set. One could argue this methodx of developing sets is actually more intuitive to the user than the “normal” approach of completely overwriting the user’s selection.

Sum of sales for Product Sub-Categories Envelopes and Office Furnishings, respecting the users selections within that field. 

Here we have the expression written with and without the asterisk operator so you can see the difference in behavior.

General Tips

You can use sets within other sets

When using nested aggregations with the aggr() function, it might be necessary to use the same set in more than one place in your expression

For QlikView, debugging your set can be done by putting your expression into a straight table and leaving the expression label blank. The dollar-sign expansion portions will evaluate in the header row. As a rule, I leave my set analysis labels blank until I have confirmed that the dollar-sign expansion is working and the expression is accurate overall.

Take care when using set analysis to label your expression appropriately. By definition, you are affecting the normal selection state of the calculation. Be sure you clearly tell the user how that expression is actually calculating. Incorporating the modifier value might be a good way to do that.x

You can use flags to identify month-to-date and other points in time to use in sets. But keep in mind these will not be dynamic (the date range evaluated will not change with date selections).

More Resources

Here are a few resources that can give you further reading.

The QlikView Set Analysis Cheat Sheet

A great cheat sheet style reference by the incomparable Miguel Ángel García.

 

Les Set Analysis

A wonderfully clear and thorough document by Fabrice Aunez that I suspect was translated from French.

 

Set Analysis Wizard for QlikView

A tool to generate set analysis expressions for your use. By Stefan Walther

Quick Intelligence – QlikView Set Analysis – Star Equals

A truly inspirational piece of knowledge from Steve Dark on the asterisk operator in a modifier.

 

Happy Qliking!

Comments on this entry are closed.

  • Gysbert April 25, 2017, 3:53 pm

    A minor nit, but one that can waste a lot of your time if you’re unlucky:

    max({$}m_sales) is not the same as max(m_sales). $ is the default set. Using no set identifier means the expression uses the inherited set. When working with alternate states the default state and the inherited states can differ. That’s when the assumption that $ is the same as not using a set identifier can get you into trouble.

  • Gysbert April 25, 2017, 3:59 pm

    There’s also a difference between Manager={‘*’} and Manager=. The former selects all non-null values, i.e. excludes the null values. The latter ignores any selection.

  • Aaron Couron April 26, 2017, 3:41 pm

    Gysbert,
    I would rather deal with minor nits than major snafus 🙂
    You are absolutely correct and I went ahead and made adjustments to the examples. Thank you so much for helping me out.

  • Héctor Muñoz April 27, 2017, 3:33 pm

    http://imgur.com/jQUMfyd

    Another examples, hope it helps.

  • Steve Dark April 28, 2017, 7:54 am

    Nice article – good to see lots of information on Set Analysis pulled into one place. I will definitely be passing this on to some of my clients.

    One thing that I don’t think is covered enough here though, and isn’t in any other documentation I’ve found either, is the use of *= instead of =. This means that users selections are also taken into account as well as the programmed set.

    I’ve blogged on this, with examples, here:
    https://www.quickintelligence.co.uk/qlik-set-analysis-star-equals/

    Cheers,
    Steve

    • Nate May 3, 2017, 3:51 pm

      Thanks so much for this!!! Somehow I’ve overlooked this for 5 years. This is a game changer. :o)

      • Steve Dark May 4, 2017, 6:21 am

        Thanks Nate – it always amazes me that this isn’t given more exposure, and it’s even more curious that it actually breaks the syntax highlighting when you use it! To my mind this is the most useful operator.

  • Aaron Couron April 28, 2017, 1:02 pm

    Steve,
    You make a compelling argument. I will probably add a use-case to this article based on your recommendation.
    I want to do just a bit more testing… What about in an example where we use the *= but then also use an additional operator in the modifier ie:
    sum({< [Product Sub-Category]*={Envelopes,Labels}-{Paper}>}m_profit)
    vs
    sum({< [Product Sub-Category]=[Product Sub-Category]*{Envelopes,Labels}-{Paper}>}m_profit)
    At first blush this seems to produce them same result regardless of selection, but as you might note when I used the same scenario with the += it did not work in the same way.

  • Ben Myers April 28, 2017, 8:22 pm

    Well written post and it’s always nice to see specifics consolidated in on place. One thing i think you missed is the implicit field operator. I find myself using these and P() in almost every instance when I use alternate states.

    Implicit field value:
    {State2}

    Possible field value:
    {State2}

    What I find interesting is that the :: implicit operator will only pass through the selections in the field. When there are selections in Field these two will return the same result. If there are no selections in Field the first set will not return results while the second one will. With a combination of these two it is possible to really fine tune when and where alternate state selections are applied.

  • Ben Myers April 28, 2017, 8:24 pm

    Strange my expressions got truncated when I posted

    Implicit field value:
    {State2}
    Possible field value:
    {State2}

  • Ben Myers April 28, 2017, 8:27 pm

    I must be getting hit with some form of content scrubbing. At any rate here’s a link to the section in Qlik Help that covers the same topic.

    http://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/Examples%20of%20Alternate%20States%20in%20Chart%20Expressions.htm

  • Shilpan April 29, 2017, 6:37 pm

    Hi Aaron,

    I love Set Analysis. Obviously, you have mastered the art of making complex topics simpler to understand 🙂

    I wrote a primer way back with a short video for a complete newbie

    http://www.learnqlickview.com/qlikview-set-analysis-basics/

    Thanks,
    Shilpan

  • Daffi May 1, 2017, 6:44 am

    Hi Aaron,

    This is really helpful!..I wish it could have been directly planted in my brain 🙂
    Maybe you’d consider elaborating about excluding values?
    There is a detailed blog by HIC:
    https://community.qlik.com/blogs/qlikviewdesignblog/2015/10/19/excluding-values-in-set-analysis

    Thanks for this beautiful post!

  • Aaron Couron May 4, 2017, 12:31 am

    @SteveDark and @BenMyers
    Added a few examples that talk about the great points you brought up. Steve’s contribution to this topic is great. On Ben’s hint, I completely forgot to include this idea. I didn’t elaborate on P() vs the double colon method to keep it as simple as possible.
    Thanks for your insights.

  • Steve Dark May 4, 2017, 6:25 am

    Hi Aaron, thanks for including my link in the main body of your post.

Join us at Living QlikView

Never miss a post.

We Never Spam. NEVER.

Show Buttons
Hide Buttons