Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More

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

MVP & Luminary
MVP & Luminary

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

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]

Attachments
Comments
Not applicable

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

0 Likes
MVP & Luminary
MVP & Luminary

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

0 Likes
Not applicable

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.

MVP & Luminary
MVP & Luminary

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

0 Likes
Luminary
Luminary

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.

Untitled.png

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

0 Likes
MVP & Luminary
MVP & Luminary

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

Luminary
Luminary

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

Version history
Revision #:
1 of 1
Last update:
‎2013-10-12 10:02 AM
Updated by: