Or-mode selections between list boxes

By Barry Harmsen

and & or selections in QlikViewAt 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:

"and" mode selection in QlikView

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:

"Or" selection in QlikView

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:

Or-mode expression

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 few years I have been specializing in QlikView and a more user-centric form of BI. I have done numerous QlikView 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.

32 Comments

  • 1
    October 23, 2013 - 14:18 | Permalink

    Hi Barry – many thanks for the dual mentions in this post and for sharing your ideas on the topic.

  • 2
    Glenn Hadgraft
    October 23, 2013 - 15:05 | Permalink

    Many thanks to Steve and Barry for taking time to crack this. It shows again what a versatile tool Qlikview is, when backed by such masterful knowledge.

  • 3
    Nate
    October 24, 2013 - 04:27 | Permalink

    Well done Barry!

  • 4
    October 24, 2013 - 07:10 | Permalink

    Excellent solution Barry. Thanks for taking the time to work it out and post it.

  • 5
    Hari
    October 24, 2013 - 09:14 | Permalink

    Thats amazing news and great work around. OR mode.. Great…

  • 6
    October 30, 2013 - 18:53 | Permalink

    wow, great solution! thanks for sharing it with us Barry. I’ve even seen some ideas in your example application that I could reuse in our developments

    • 7
      October 30, 2013 - 22:38 | Permalink

      Hi Marcos,

      Glad you liked it. Out of curiosity, which ideas did you find useful?

      Cheers,
      Barry

  • 8
    October 31, 2013 - 13:38 | Permalink

    Hi Barry.

    To begin, the script to generate random data, I liked it. The use of the field “$Field” toghether with CONCAT in vSelection.Or variable and the whole construction of the variable; use of different variables and their combination to generate the final expression of Sales, and finally the solution of the example to be able to use the OR-Mode

    All these parts of the example have shown me an interesting use of QlikView options that I know, but I liked how you put it into practice

  • 9
    Vlad
    November 6, 2013 - 01:09 | Permalink

    Hi Barry,
    for some reason I can’t download a example file. It gives me Error (404). Can you help, please?

  • 10
    Andres Kukke
    November 6, 2013 - 13:54 | Permalink

    Hei Berry!

    Nice job. I tried to download example, but it didn’t work. Can you check dropbox link ?

    Andres

  • 11
    November 6, 2013 - 16:37 | Permalink

    Vlad, Andres,

    I accidentally deleted the file, it’s been restored now.

    Kind regards,
    Barry

  • 12
    November 29, 2013 - 12:38 | Permalink

    There is a built in ‘and’ mode. It requires you to modify the data model http://blogagilos.wordpress.com/2011/07/15/tip-1-the-qlikview-and-mode/

    Maybe thats something abit different though?

  • 13
    Carol
    January 2, 2014 - 03:38 | Permalink

    Hi Barry,

    This is great. Thank you for the solution. It works perfectly when I apply it to 10 fields. However, I tried to do it with a survey result where there are brand perference questions. For example, do you prefer to chocolate brand A, B or C? There are lots of questions like this that results in thousands of fields. The solution doesn’t work in this case.

    Do you know the maximum variables that it can take? Or is there other tweaks that I need to do to make it work?

    Thanks,

    Carol

    • 14
      Martijn ter Schegget
      May 8, 2014 - 23:47 | Permalink

      Hi Carol,

      I guess there’s some limit in the length of an expression, and with this solution you’re basically generating an expression that’s longer than the combined length of the (in your case thousands of) fields you’re trying to perform an ‘OR’ on.

      Last year I’ve tried to build a variant on this solution based on the {1} set instead of {$} (so I didn’t have to clear lots of fields using ‘Fieldname=’ set modifiers), but didn’t quite pull it off. 🙁
      If anyone manages to optimize Barry’s solution using this strategy I’d also be interested.

      Regards,
      Martijn

  • 15
    James
    February 26, 2014 - 16:03 | Permalink

    Hey Barry,
    I like your solution. I need to get it to work on mine. But when I put it in it doesn’t work. I put it in this way. It won’t make the vSelection. Any ideas?

    SET vSelection.Or=$(=
    Concat(DISTINCT {}
    ‘If(GetSelectedCount([‘ & [$Field] & ‘]) > 0, ‘ & chr(39) &
    ‘<[' & chr(39) & ' & Concat(DISTINCT {} [$Field], ‘ & chr(39) & ‘]= ,[‘ &
    chr(39) & ‘) & ‘ & chr(39) & ‘]= > + ‘ & chr(39) & ‘)’ , ‘ & ‘)
    );

    Thank you.
    J

  • 16
    Dip
    April 9, 2014 - 20:59 | Permalink

    Hello ,

    I am not able to download the file. It says “Web Page Not Available”.

    Please suggest how can I get the example file?

    Thanks,
    Dixit

  • 18
    Gary Strader
    June 17, 2014 - 22:52 | Permalink

    Barry,

    This is a very clever solution, but I have a potential problem. The scenario is that we add a Customer 4 which is associated with a Product 4, and no other customers are associated with Product 4. When I start by filtering on Customer 1, Product 4 will be grayed out/unassociated. When I then select the grayed out Product 4, QlikView will force Customer 1 to unselect. It seems that this method only works when everything is associated with everything?

  • 19
    rughmin
    July 18, 2014 - 04:45 | Permalink

    hi.
    sorry, i don’t know english..

    I’m qliview studying.

    i want this example file.

    please.

  • 20
    Majid Saeb
    November 4, 2014 - 09:54 | Permalink

    Thanks for your topic! is great. But how can use in een datmodel without autogenarate.

  • 21
    Renuka Mamidi
    November 12, 2014 - 20:44 | Permalink

    Is there a way to overrride the grey behavior of qlikview. Looks like the ‘or’ selections work but still show up grey in the example attached in this blog.

  • 22
    rohan
    January 27, 2015 - 14:30 | Permalink

    Thanks for the solution. We have reached till this point in our application, however challenge is that how can we get this logic working in Section Access.

    Considering the above example how can i restrict one particular user the application access for Product2 or Salesman1

    In our case we are trying to restrict user to access RegionA = ‘Asia'(Region B can be asia or anything else) or RegionB = ‘Asia'(Region A can be asia or anything else)

    So User has access to Region A = asia or Region B = asia

  • 23
    Fra
    April 23, 2015 - 11:10 | Permalink

    Hi, I’m not able to download solution… How can I do?

    Best regards
    Francesco

  • 24
    Manish
    April 25, 2015 - 16:30 | Permalink

    Hi Barry,
    I can see that most of the files for your examples are not available to download from Dropbox link you have provided.
    Can you check this and come back to us?

  • 25
    Rajinder
    June 3, 2015 - 12:19 | Permalink

    I am not able to download the file from dropbox. Can somebody please send me the file.

  • 26
    Lianne
    October 7, 2015 - 11:41 | Permalink

    What a genius idea! Unfortunately I am not able to download the file. Can you please send me the file?

  • 27
    Lynette
    December 1, 2015 - 15:26 | Permalink

    Hi,

    I am very interested in your solution to this issue, but am unable to download the example.

    Could you send it to me?

    Thanks!

  • 28
    laura
    July 15, 2016 - 13:45 | Permalink

    Please send me your example plis.. Your page is not working im this time…
    thank you so much…

  • 29
    Josh
    July 26, 2017 - 20:37 | Permalink

    Does anyone have this example file they can share please?

  • 30
    Shannon Marshall
    August 23, 2017 - 16:27 | Permalink

    Can you please upload solution again. The Dropbox file is unavailable.

  • Leave a Reply

    Your email address will not be published. Required fields are marked *

    Read previous post:
    Hosting issues: QlikFix is back!

    I had it coming, but yesterday it finally happened; the QlikFix website collapsed under its own success. With visitors and...

    Close