At the Masters Summit for QlikView in London we ended the second day with an informal ‘stump the geek’ session. In this session participants could submit technically challenging questions to a panel. Besides the four presenters, this panel also consisted of Steve Dark, Stephen Redmond and Henric Cronström. You can imagine that I found myself surrounded by an awesome amount of QlikView knowledge.

While challenging, most of the questions could either be answered on the spot or a conceptual solution could be described. There was however one question that didn’t really yield an, in my opinion, satisfactory answer. The consensus almost seemed to drift towards ‘it cannot be done’ or ‘that is not how QlikView works’. The question was:

Can we make “or” selections between list boxes, instead of the standard “and” selections?

And & or selection modes

To explain that question a little more, consider the following example of sales amounts by customer, product and salesman:

By default, selections within a list box are done in or-mode. By selecting Customer 1 and Customer 2, we select all records that are associated with either Customer 1 or Customer 2. By contrast, selections between list boxes are always done in and-mode. Selecting Product 2 and Salesman 1 returns all records that are associated with Product 2 and Salesman 1.

The question was if we could override the selection mode between list boxes. In other words, if we could return a result that showed all records associated with either Product 2 or Salesman 1.

At that moment, no one could think of an easy moment to achieve this. While I had some general ideas about where to look I wasn’t able to clearly articulate those at the time.  We were stumped, or at least, I was.

A possible solution

Of course, to someone who likes solving problems there is nothing more challenging than a problem that appears unsolvable. Impossible? We’ll see about that! (I like how I am not the only one who accepted this challenge by the way, Steve Dark has also posted some of his thoughts and solutions to the QlikCommunity.)

After taking some time to think it through, I came up with a preliminary solution. Now that the Masters Summits have finished and I’ve finally caught up on my day job again, I found the time to turn this into a more presentable solution:

The image above shows the exact same selection as the earlier image. However, when you look at the straight table you will notice that the selection has now been made in or-mode. Data is shown when the Customer is Customer 1 or Customer 2 or when the Product is Product 2 or when the Salesman is Salesman 1. This is achieved using a Set Analysis statement that is built using the following (simplified) logic:

1. For each field in the data model that is not in the excluded fields list;
2. Check if selections have been made in the field using GetSelectedCount();
3. If selections have been made, generate a Set Analysis statement clearing selections in all other fields, except fields in the excluded fields list;
4. When all fields are processed, union all the Set Analysis statements together (note: I will expand the post later to explain this concept in some more detail);
5. Use the unioned Set Analysis statement in your expressions.

A variable is used to store a list of fields to exclude from the or-mode. An example of fields to exclude are calendar fields. Showing sales that were made in the year 2012 or by Salesman 1 doesn’t really make any sense. The expression that I use to generate the Set Analysis statement is shown below. You can imagine that it was quite a beast to write:

What I like about this solution is that it’ll work in any data model, there is no need to predefine things in your data model or create alternate states. There’s also a few things that could be improved though:

• Create an additional statement that doesn’t work on all fields in the data model, but only on those fields specified in an included fields list. This should be a relatively easy change.
• The current expression clears all selections except for the active field. Rewrite the expression so that it only clears selections in fields that actually have selections. Conceptually a very easy change, but might require some more tweaking to get it into the expression without errors.

Performance is another important aspect. In the test I did with 10,000 row dimension tables and a 10,000,000 row fact table this solution actually performed quite well. Of course I haven’t applied this to any real world solutions, but who knows if it might prove useful in the future?

Download the example file

You can download the example file below. In it you will find the expressions that I used, which you can copy to your own applications. Of course, if you improve the expressions then I’d like to hear from you.

Download the example application

Your thoughts?

So what are your ideas? Is this something you should even be doing in QlikView? Is there a better way to achieve this? I’d be very interested in hearing your thoughts and feedback.

About The Author

Barry Harmsen

Hi there, I'm Barry and I'm a Business Intelligence Consultant at Bitmetric and based in the Netherlands. Originally from a background of 'traditional' Data Warehousing, Business Intelligence and Performance Management, for the past 10 years I have been specializing in Qlik and a more user-centric form of BI. I have done numerous QlikView and Qlik Sense implementations in many different roles and industries. In 2012 I co-authored the book QlikView 11 for Developers. You can follow me on Twitter at @meneerharmsen.