AND-Mode

[Jennell_Yorkman]

Have you ever wished the QlikView list box used a logical AND instead of a logical OR when you made selections?  Well, AND-Mode is a hidden QlikView gem that allows a list box to be changed from the default logical OR to a logical AND.  By default, if one or more items are selected from a list box, the other fields will display data that is associated with any of the selections.  When using AND-Mode, the other fields will display data associated with all the selections.

In order to add AND-Mode functionality to your QlikView application, you need to:

• First, include a table in the data model that stores the field that will be used in the AND-Mode list box.  The records in this table must be distinct and the table can only have two fields, one of which is a key field and the other which is the field used in the AND-Mode list box.  Here is an example of what this data model may look like:

• Second, in the user interface, add a list box and check the And mode checkbox on the General tab of the Properties window to enable the AND-Mode functionality.

Now the list box will use AND logic versus OR logic.  In the screenshot below, I can use the AND-Mode list box to view customers who purchased both Bologna and Cheese.  Notice the amperstand (&) in front of the selected values indicating that we are using AND-Mode.

You can go one step further with the AND-Mode functionality and make NOT selections.  For instance, I can see which customers purchased Bologna and Cheese and not Sliced Bread by also selecting Sliced Bread from the AND-Mode list box.  To make a NOT selection, you need to click and hold the selection a moment until it turns red as seen in the image below.

Now I can see customers who purchased Bologna and Cheese but did not purchase Sliced Bread.  AND-Mode is quite powerful and can be added to any list box as long as the data model criteria are met. 

Check out this technical brief for step-by-step details of how to implement AND-Mode functionality into your QlikView application.

Jennell

[Anonymous]

Nice Explanation. Thank you.

[#3]

Hi Jennell.

Do you have de app exemplo? Can you please share with us?

Thanks!

[#4]

Hi Jennel,

thank you for your Document,

I try to make it in my application and it work very well but I have a small porblem:

So how can I do if I just wan to see ONLY the select andmode  value?

I have on the left side the listbox "AndQuality" on the right side I have a Table (Pivot Table or...) and in this table I just wann to have ONLY the ID with the select AndQuality values (Only the green Values)

Correct ID = 899, 969, 970, 1050

Wrong ID = 761, 795,796

Thank you for your time and answer

[Jennell_Yorkman]

Hi Armand,

I think you need to use a regular list box that by default uses a logical OR when you make selections.  Then the items in your table will only be for the selected items.

Hope this helps.

Jennell

[datanibbler]

Hi Jennell,

I see the use of this now. But I cannot do this with a field from just any table, but I need a dedicated table with just two fields for it?

Thanks a lot!

[#7]

Hi Jennell,

Thank you very much for your detailled message describing the "And-" and "OR -Mode" in Qlickview.

Does something similar exist in Qlik-Sense?

Best regards

Hassan

[JonnyPoole]

Hassan - i have not seen AND mode in qlik sense, but i did use some SET ANALYSIS to treat multiple selections in a list box as an 'AND' for purposes of rendering a chart

Here is my technique. I'll try to add it into a blog post on the community some time

No Selections:   AND mode chart ,  OR mode chart

When i select skills  A, B , C ... only 1 user has all 3 but three users have 1 or more of those selected skills.

Here is the measure expression for the AND mode

...which basically means, show me the users that have the same number of distinct skills as the number of skills that i selected in the filter pane.

by comparision, The 'OR' mode has this expression

count(distinct Skills)

[JonnyPoole]

Just adding a link to a sample APP that i posted in the last few mins. Should help folks create their own samples

AND mode in Qlik Sense

[sakamsureshbabu]

Hi Jennell,

Nice document

[#11]

Hi Jonathan,

thx for the nice explanation and for the attachment. I use it in one of my app´s and it works fine.

best regards

Hassan