Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
Jennell_McIntire
Employee
Employee

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:


data model 2.png

  • 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.


List box properties.png

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.

And.png

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.

Not.png

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

21 Comments
paulyeo11
Master
Master

Nice post , by the way may i know how to generate the field name = [item branch key] ? which consist of customer ID and purchase items from product sub group desc or product group desc.

0 Likes
4,005 Views
Jennell_McIntire
Employee
Employee

Hi paulyeo11, I am not certain how the field was created but it was a key created to link the ItemMaster and SalesDetails tables.

0 Likes
4,005 Views
Not applicable

Hello Jennell,

Using your example above what if you wanted to add an additional dimension, for example, who purchased bologna and cheese in 2011? Who purchased both in 2012, etc.?

0 Likes
4,005 Views
Jennell_McIntire
Employee
Employee

You can add a default Year list box to see how the data differs from year to year. 

If you wanted to compare them side-by-side then I would recommend using comparative analysis/alternate states.

4,005 Views
Not applicable

Thanks Jennell for the follow up. Would you mind posting a sample qvw file where the "Year" list box can be applied to the and mode example? In that case, one would be able to select the "Year" and see customers who purchased bologna and cheese in that year, then clicking on a previous year would show a different number of customers who purchased both, etc.?

0 Likes
4,005 Views
datanibbler
Champion
Champion

Hi Jennell,

that sounds very interesting - but I cannot quite grasp what is the difference between this and just pressing the Ctrl_key when selecting several values from a listbox?

Could you please clarify this for me?

0 Likes
4,005 Views
Gysbert_Wassenaar

See this blog post for a great explanation of the difference: AND and OR

0 Likes
3,179 Views
datanibbler
Champion
Champion

😉

Good one, Gysbert - that blog_post

- is excellent, no doubt about that AND

- it references - you guess - THIS one

😉

That's why I thought I'd ask my question here.

0 Likes
3,179 Views
Not applicable
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.


Nice. But if I want to see which customers not purchased Sliced Bread IN PERIOD... How can I do this?

0 Likes
3,179 Views
Jennell_McIntire
Employee
Employee

Hi Gertye,

If I understand your question correctly, you would make the NOT selection in the list box for Sliced Bread and timeframe selection(s) in another list box such as date.  The example QVW I refer to does not have any dates but I think it should work if you had a data model with a date field.

Jennell

0 Likes
3,179 Views