Set Analysis is a powerful set of syntax which is widely used in QlikView and Qlik Sense applications, but I find that many developers are unaware one of the most useful functions. Here I explain how to use it.

Set Analysis for QlikView and Qlik Sense

Qlik developers have a lot of syntax to learn. From SQL statements, load script, expressions and VB macro code. Set Analysis is a subset of expression syntax that can cause some developers issues.

It is however very simple at heart. You can add a Set to an expression which changes the selection context for that expression – effectively forcing selections that only apply to that one expression. So sum(Value) becomes sum({<Type={‘Relevant’}>}Value), if you only want to add up the values which are flagged as being Relevant in the data.

By nesting Set Analysis syntax, and using different operators, highly complex expressions can be built. They are all however just expansions on the very simple concept of applying selections on a field.

Counter Intuitive Behaviour

One of the problems with Set Analysis is that the results can be counter Intuitive to users. When Qlik products are explained to them, they are told that their selections effect everything. Set Analysis expressions, however, modify those selections. This change should always be clearly signposted to users with labels and legends.

For things like prior period comparison, Set Analysis is brilliant. Caution should be taken though not to confuse users.

Enter Star Equals

A common use case for Set Analysis is to tie a chart or an expression to a specific dimension value. Generally I see this implemented in people’s code with an Equals operator, as in the example above. This overrides the users selection. Another operator that can be used is Star Equals, this gives the intersection of what is specified in the Set and the users selection.

Take this example, shown with no selection made:

QlikView Set Analysis - No Selections

You will note that Equals and Star Equals give identical results above. Look what happens though when we select a single Region:

Set Analysis Midlands Selected

With Equals the users selection is totally ignored, but with Star Equals it is respected. This is far more intuitive for users – as you instil in them that selections affect all objects, but Set Analysis without *= then breaks this.

For prior period comparison it may be that you want to ignore the users selection, so if they select only 2016 you still want to show 2015 as the comparative period. Generally though I think the Star Equals is the better approach.

A similar approach can be applied on both sides of a comparison expression. Take for example these four different gauges, all snapped under the context of having “South” selected, and with different Set Analysis statement (or lack of) on both sides of the equation:

Set Analysis Gauges

To explain the gauge results one at a time;

  1. North is a fixed selection on the left side, but the denominator is taking the selection into account – you therefore get North as a percentage of South (which makes little sense).
  2. North is applied with *=, so with North not in the selection it shows zero %.
  3. North is fixed as the region being used as the part, and with region ignored for the denominator (using = ) the percentage shown is exactly the same as if there was no selection made.
  4. North is being shown as a percentage of all regions, and without North as part of the selection (and with a *= ) it shows exactly the same as the second gauge.

This shows there are options around how you apply set analysis to your expressions, and you need to think carefully about what you apply. Even in this simple example with a single field to be selected there is much to consider.

Why Is This Not Used More?

The main reason, I think, that so few developers that I speak to know about this function is due to a bug in QlikView. For many versions the use of Star Equals gave a wiggly red line, indicating bad syntax. This would put off all but the most confident developer! The syntax highlighting however is only a guide, and sometimes you just have to push on and ignore it. It does make the rest of the development of that expression more tricky, but there you go.

Once you start using the correct expression syntax then many of the issues of apps being counter intuitive can go away, and users can feel there selections are not being ignored.

Further Reading

I’ve deliberately not tried to comprehensively cover Set Analysis here. There are many great resources for this already. The best guide I have found, which does dig quite deep, is the section in QlikView 11 For Developers, by Barry Harmsen and Miguel Garcia. As well as covering all operators and modifiers (including the slightly newer P and E functions) it has a set of handy Venn Diagrams showing what is included when any operator is used. A quick Google search will also turn up a lot of results, including many on Qlik Community. We also cover the topic in more depth in our online Sense training courses.

The app that has been used to grab the screenshots here can be downloaded from Qlik Community here:

Using this example you can see exactly what happens to each of the expressions when you apply different selections.