Qlik Community

Ask a Question

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Announcements
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
2,650 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
2,650 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
2,650 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.

2,650 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
2,650 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
2,650 Views