Qlik Community

Qlik Design Blog

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

Employee
Employee

AND-Mode

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
Valued Contributor II

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
130 Views
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
130 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
130 Views
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.

130 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
130 Views
datanibbler
Esteemed Contributor

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
130 Views

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

0 Likes
130 Views
datanibbler
Esteemed Contributor

;-)

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
130 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
130 Views
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
130 Views
nakul1001
Contributor II

Nice Explanation. Thank you.

0 Likes
130 Views
Not applicable

Hi Jennell.

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

Thanks!

0 Likes
130 Views
Not applicable

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

0 Likes
130 Views
Employee
Employee

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

0 Likes
130 Views
datanibbler
Esteemed Contributor

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!

130 Views
Not applicable

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

0 Likes
130 Views
Employee
Employee

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

AndMode1.PNG

No Selections:   AND mode chart ,  OR mode chart

AndMode2.PNG

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

AndMode3.PNG

Here is the measure expression for the AND mode

AndMode4.PNG

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

130 Views
Employee
Employee

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

130 Views
sakamsureshbabu
Contributor

Hi Jennell,


Nice document

0 Likes
130 Views
Not applicable

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

0 Likes
130 Views
ciampaciampa
New Contributor II

Jennell McIntire ha scritto:

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.

Jennell,

Never too late...

So surprised that someone in QlikTech talks about the "And-Mode".

In my experience, just few people, customers or Qlik technicians, have

understood its value and used that feature.

And...it seems that even no one in the QV R&D really

understands its value, if it's true than this feature it's no longer supported in QlikSense but

trust me, SQL skilled people, appreciate it a lot. 

When in 2008 for the first time I studied the and-mode feature I recognized immediately

its huge value. Why? Because I know very well how hard is to answer to the same query by a SQL statement.

Said that, I want to warn that the mix of an "And-Mode" list box and a "standard" list box doesn't work.

To do that, I attached a QV example.

I hope to contribute to the discussion...

Regards

AndMode.gifAndMode_mysolution.gifAndMode_script.gif

0 Likes
130 Views