When you select multiple values of a single filter in QlikView, the native behavior is an "OR" operation‒charts will display data points that are associated with any of the selected filter values. This article will show you how you can easily create "AND" (and "NOT") filter boxes in QlikView without any data model modifications!
There are two fields that will be involved in achieving "AND" functionality: (1) the field which we are filtering (let's call this the "filter field"), and (2) the field that we want to be affected by the filter (the "relevant field"). For example, suppose we have a typical sales application that contains customer and product information. We may want to set the filter field to be "Product" such that we can filter in the Product list box with "AND" functionality. We can decide to make the "relevant" field "Customer." If we do, and we select two products, then we are requesting that those Customers that have associations to both of those products be returned. Alternatively, we can set the relevant field to be "Month"‒that would cause those Months that have associations to both products to be returned.
QlikView itself has a native way to achieve "AND" list boxes; if you wish, you can read more about it here
. This approach is quite complex and involves significant data model manipulations. In addition, wildcard searches on filter field values are not possible‒users would need to explicitly select all values they wish to include in the AND operation. Finally, there is no way to change either the filter field or the relevant field on the fly; changes would need to be made in the load script. However, as mentioned above, in the approach that we will discuss here, no data model changes are needed at all. Wildcard searches will also be possible and, as an added bonus, it is quite easy to use variables to define which fields to set as the relevant and filter fields. All this is possible thanks to QlikView Alternate States.
Alternate States in QlikView permit contradictory selections to be performed within the same field, as long as the filter boxes exist in different states. We can, therefore, create two additional states (let's call them AND and NOT) that will exist in parallel to the "Default" state, which is where our charts will reside. We can then create filter field list boxes in each state and use two variables to capture the states' field selections. Specifically, we will use the P() and E() functions in set analysis in combination with both the filter and relevant fields to create set variables that will, respectively, define possible and excluded sets. These set variables can then be inserted into the set analysis of any chart in the Default state. This method has been implemented in the following app, which is available for free download:
The first real limitation to the approach described in this article is that it will apply to charts only, and not to list boxes that are in the Default state. Meaning, selecting multiple values in the filter field list boxes in the alternate states will not cause non-chart objects in the Default state to display appropriate values of the relevant field (at least not without implementing aggr() list boxes that use set analysis, which can be prohibitively heavy).
The other limitation is filtering on all values of the filter field (e.g. showing customers that are associated to all products). Since it is not possible to use functions getfieldselections() and getselectedcount() with set analysis, the only way to check whether any selections have been made in the filter field is through aggregation functions such as count() or concat(). However, these functions will return the same thing when no values are selected as when all values are selected. It is up to you to decide what behavior you would like to occur when this happens‒in the example above, I decided to treat that situation as "no filter values selected." While you can easily modify the set analysis variables to treat it as "all filter values selected," it is necessary to understand this limitation in advance. Update: this limitation has been removed in QlikView version 11.20 SR8 or higher. An updated example has been uploaded to reflect these new QlikView capabilities.
I hope you will agree that these relatively minor limitations are vastly outweighed by the benefits of this approach over the native QlikView solution to the "AND" problem. Please feel free to share in the comments section below your personal success stories using our approach! And, as always, happy Qliking!