QlikView Set Analysis – Star Equals

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 same context (“South” selected), and with different Set Analysis (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.

Barry also delivers an online interactive learning session that focuses purely on Set Analysis, visit http://q-on.bi/courses/qlikview-set-analysis/ for more details.

The app that has been used to grab the screenshots here can be downloaded from Qlik Community here:
https://community.qlik.com/docs/DOC-17586

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

By |2017-03-06T10:37:48+00:00October 25th, 2016|Qlik Sense Tutorials, QlikView Tutorial|14 Comments

About the Author:

Steve is owner and principal consultant at Quick Intelligence. He is a Qlik Luminary, Qlik Community MVP and Technical Editor of a number of QlikView Books.

14 Comments

  1. Ranjan October 25, 2016 at 11:30 am - Reply

    It’s very knowledgefull ….
    Thanks

  2. malika ouenza October 25, 2016 at 12:27 pm - Reply

    thank you so much that was so helpful. I applied it already

  3. Aaron Couron October 25, 2016 at 12:44 pm - Reply

    I am embarrased to admit I wasn’t aware of the “*=”. You have added to the field of knowledge in set analysis which is no small feat.

    • Steve Dark October 25, 2016 at 12:52 pm - Reply

      Thanks Aaron. I picked up a few tips from your Sense Master Items post today also – so we are quits!

  4. Michael Terenzi October 25, 2016 at 9:41 pm - Reply

    Awesome post Steve! Thanks for sharing

  5. Donnie Clark October 28, 2016 at 6:47 pm - Reply

    Obviously there’s more use cases for *=, but you can actually do += and /= as well. += will do an “or” between your selections and the values in your set analysis and the / will do the “in one but not both”.

    Not sure of any times where you’d want to do those, but they are possible.

    • Steve Dark October 28, 2016 at 8:57 pm - Reply

      Thanks Donnie. There is much that can, and has, been written about set analysis, which is why I wasn’t going to replicate it all. These are very good tips to have in the comments. Thanks!

  6. pavan November 20, 2016 at 1:31 pm - Reply

    Really helpful and good knowledge

  7. RJ November 29, 2016 at 8:21 am - Reply

    “Counter Intuitive Behaviour”

    Excellent post. I almost always use *= now and have done for some time. Especially since moving to SENSE. I have also stopped ‘helping’ users so much with SENSE. My view is it is easier to make selections in SENSE. And then to see the selections made on the brilliant black bar (or remove them). So I try not to build in limits using set analysis unless there is no other option.

    For example I try to avoid a YTD set analysis. Users can easily select the months as required. It’s a you’re in control so make sure you make the right selections (self service) approach. As opposed to helping them using set analysis and triggers (guided analytics)

  8. Bibhu Sharma December 1, 2016 at 11:15 am - Reply

    Excellent post Steve. Thanks. It’s new to me.

  9. […] QlikView Set Analysis – Star Equals. “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.” […]

  10. […] Quick Intelligence – QlikView Set Analysis – Star Equals […]

  11. Narendra July 20, 2017 at 8:59 am - Reply

    Hi

    how to handle nulls using set analysis? possiable are not

    • Steve Dark July 20, 2017 at 10:32 am - Reply

      You are correct that you can not select nulls using set analysis, well, at least not directly. ={“*”} will give you all non-null values. You can therefore use -={“*”} to pick up all null values.
      Hope that helps.

Leave A Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.