QlikView App: Create list boxes that work in an OR mode

[stevedark]

In October 2013 the QlikView Masters Summit came to London.  During the event there was a Quiz the Masters session and the question was asked was there a way to create ListBoxes that work in a OR fashion rather than an AND.

At the time no one could think of a simple way of achieving this - but I thought I would try a few things out.  This document is some of my workings.

The first attempt was based on creating a linked table with all values in for each field that was to be included in the and.  These would be split into separate list boxes with Expressions in the List Box.  Unfortunately although those selections should not be mutually exclusive - from a data model perspective - they are.

The second attempt kind of worked - but looked pretty bad.  The values from each of the fields were placed in a single field with a prefix showing which field they had come from.  Selections could then be made from values from each of the source fields and the resultant selections did give an AND of the values.  It was a kind of solution.

The third attempt works and the list boxes look exactly like the natural list boxes.  This is achieved by loading every value for each field that needs to be used in the OR into a separate table stored as a Data Island.  By changing any expressions to use Set Analysis and a P modifier looking at the data islands values can be found for any rows where a selection is made in any of the fields.  This ticks the boxes (I believe) for answering the question.

However.... I would really not recommend the third approach as it requires duplication of much of the data model in memory and could perform very badly.  Also, if a user was to pick one of the data island fields when they thought they were picking an actual dimension things could go very bad with a Cartesian product occurring.  Perhaps a Hide Prefix on those fields would make it less risky though.

So, at the end of the day - this is very much open for discussion and I would welcome any further suggestions on how this could be achieved.

Happy Qliking

Steve

http://www.quickintelligence.co.uk/qlikview-blog/

[Note: there is a function called AND MODE in QlikView and this works in simple cases.  It wasn't deemed appropriate for this solution though]

[#2]

Alternate states is another option.

The set analysis would be something like below:

sum({$<State1=Dim1::Dim1>+<State2=Dim2::Dim2>} Value)

I uploaded a modified version of your QVW file.  http://community.qlik.com/docs/DOC-5096

Karl

[stevedark]

Hi Karl,

Thanks for your thoughts on this.  I generally avoid using Alternate States - as I find it can make things counter intuitive for the user - but this is a perfect example of how it can be used though.  Looking at your example, should the expression not be as follows?

sum({$<Dim1=P({State1}Dim1),Dim2=>}Value)+sum({$<Dim1=,Dim2=P({State2}Dim2)>}Value)

Cheers,

Steve

[#4]

Hi Steve,

I agree with your point of view on alternate states.  I only think of using them when any data islands are used. 

Sorry, I made changes to the formula in the post without testing it.  I put the name of the state where I should have put the name of the dimension.  This formula seems to work fine.

sum({$<Dim1=State1::Dim1>+<Dim2=State2::Dim2>} Value)

I updated the file accordingly.http://community.qlik.com/docs/DOC-5096.

[stevedark]

Hi Karl,

I can see that syntax now works, and it pointed out to me that my code could be simplified thus:

sum({$<Dim1=P({State1}Dim1),Dim2=>+<Dim1=,Dim2=P({State2}Dim2)>}Value)

The overriding of the values which aren't set by the P being required only if selections are possible in the default state - as well as the two alternate states.

Your method of referencing values from other states directly is very neat, I will need to remember that if I do more with alternate states in future.

-Steve

[barryharmsen]

Hi Steve,

This is a very ugly hack, but it works. I'm generating a Set Analysis statement by using the following expression:

=$(=

Concat(DISTINCT {<[$Field]-={$(FieldIgnore)}>} 'If(GetSelectedCount([' & [$Field] & ']) > 0, ' & chr(39) & '<[' & chr(39) & ' & Concat(DISTINCT  {<[$Field]={"*"} - {' & $(FieldIgnore) & '} - {' & chr(39) & [$Field] & chr(39) & '}>} [$Field], ' & chr(39) & ']= ,[' & chr(39) & ') & ' & chr(39) & ']= > + ' & chr(39) & ')' , '  & ')

)

Basically, this goes over every field in your app and if it is selected generates a set analysis statement clearing all selections except that field. Next, all these set analysis statements are unioned. The fields listed in the variable FieldIgnore are excluded from this expression, as you'll probably not want to do or-mode on things like calendar fields. The nice thing about this is that you do not have to specify the fields to monitor up front. Not sure how well it performs though

The picture below shows an example. The Customer field is ignored (so it's in and-mode), the other fields are either Product 2/3 or Salesman 1/2.

Currently at the Masters Summit, so don't have much time to polish it up, will post a full example after the event.

Kind regards,

Barry

[rwunderlich]

I recall working on a similar question back in June here and developing some examples.

http://community.qlik.com/thread/83885

Those examples are for two specific fields.

-Rob

[barryharmsen]

Here's the solution I came up with, a beast of an expression, but it works: Or-mode selections in QlikView The Qlik Fix!