And-mode in list boxes reminds me of one of my favorite characters — Sherlock Holmes. It sounds esoteric but, believe me, it plays an important role in the world of data discovery.
When user makes multiple selection within a list box, QlikView engine interprets that as logical OR. In other words, all the related fields from any of these selections will get displayed due to their association with any of the selected field values.
This works well for most cases but sometimes, business user may want to know who sold certain product in past three consecutive years or past three consecutive months(which means, those who did not sell certain product in any of the past three years or past three months should be eliminated from the list).
Enter And-mode in the list boxes to be your Sherlock Holmes.
Back to Basics
Let’s review prerequisites to enable And-mode in a list box.
-
And-mode requires that the field has to be the second field in the table of 2 fields. First field links the table with other tables in the data model.
-
Only distinct values need to be loaded in the table to avoid duplicates.
-
When And-mode is activated, “&” will be displayed in front of the selected values. A forced exclusion will add “!” in front of the selected values.
-
Once these requirements are met, check And-mode in the general tab of the list box properties.
Okay, now it’s time to put this knowledge into action. I will also show you why Set Analysis plays important role as well.
Let’s create a simple inline table and a 2 field table to enable and-mode.
As you can see, Alex Bond has made sales in each year between 2010 and 2013 but John Smith has not made any sales in 2011; likewise, Bob Barr has not made any sales in 2012.
With that insight, we know that if user selects all three years in a list box with and-mode enabled, only Alex Bond should show up in the related tables.
So, let’s create a list for the field SalesYear.
Once we enable And-mode for this field, an ampersand (“&”) will be displayed in front of each selected value.
Let’s create a straight table with Year and SalesPerson as dimensions and sum(Sales) as expression.
If user selects 20111 and 2012, straight table shows records for Alex Bond but it also shows record for 2010.
We need to use Set Analysis to show related records for selected years in the SalesYear field.
Purgechar function removes either & or !(forced exclusion) characters from the field selection. You can not only select several values but also exclude certain value from the selection by clicking on a value and keeping mouse button pressed until the cell turns red.
Now, as an astute detective, you can find out who sold for consecutive years without including insignificant year.
Elsewhere:
1. AND Mode in Set Analysis via Qliktips
2. AND-Mode via QlikView Design Blog
