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:
Limitations
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!
Thank you. The original decision. Will use. Sometimes it helps not the standard way of thinking.
“Since it is not possible to use functions getfieldselections() and getselectedcount() with set analysis”
With set analysis? You mean with alternate states?
Since SR8 you can use these functions with alternate states!
Sander, yes I meant you could not use set analysis within these functions. 11.20 SR8 was released less than 2 weeks before this article was written so I did not include its new capabilities (because I did not have a chance to test them for myself). You are correct though, the addition of “StateName” to getfieldselections() and getselectedcount() will change the way a lot of alternate state logic is written and removes many limitations that we previously had.
I just uploaded an updated example that incorporates new logic when 11.20 SR8 or higher is detected.
Cheers,
Vlad
Vlad, this is exactly what i need to do… But i did not understand comlytely the way you do it! Especially how you get variables from alternate states to make a chart! Кстати, вы по русски не говорите? ))
Boris, if you could narrow your question down to specific syntax that confuses you, I’d be glad to explain.
Regards,
Vlad
Helo, Vlad!
I have got your idea in general, but i’m not a great master in QV so i dont understand the final step in generating chart table.
You put in expession section this record: sum({$(vAndProducts)$(vNotProducts)}Sales)
And in dimension section: =$(='[‘&vRelevantField&’]’)
In variable declaring section vRelevantField=Product
I did not got the idea.
Could you be more detailed in explanation in this case?
Regards,
Boris.
And one more thing – my vAndProducts variable is a litle bit more complex (for example):
vAndProducts=<Владелец=P({} Владелец)>*<Владелец=P({} Владелец)>*<Владелец=P({} Владелец)>*<Владелец=P({} Владелец)>
So maiby i can not get a right result because of it?
And one more question – in variable declaration section:
vAndProducts
=
/*
Updated for new capabilities in 11.20 SR8
11.20 SR8: checking actual selection count in alternate state
Earlier versions: checking whether some filter field values are selected (but not all)
*/
if((v1120SR8 and getselectedcount($(='[‘ & vFilterField & ‘]’),0,’AND’)>0) or (v1120SR8=0 and count({AND} distinct $(='[‘ & vFilterField & ‘]’))count({AND} distinct $(='[‘ & vFilterField & ‘]’))),
concat({$*AND} distinct ‘<' & vRelevantField & '=P({} ‘ & vRelevantField & ‘)>’,’*’,Product),
” //filler so set subtraction works
)
Why you put word “Product” as last argument
What does in mean? Database field? Why non variable?
Absolutely not clear for me ((
Boris,
To your first comment, a detailed explanation of what I’m calling “Filter” and “Relevant” fields can be found in this article in the second paragraph.
To your second comment, perhaps you forgot the include the word “distinct” in your concat() function? I also notice your P() set is empty, which means you didn’t define a filter field.
Third comment: the third parameters of a concat() function designates the sort weight. Meaning, selected products will be concatenated alphabetically in this case. Just keeps it cleaner.
In general, it sounds like maybe you just downloaded the QVW but didn’t read the article. Could be helpful to do so.
Cheers,
Vlad
Helo, Vlad!
I read an article first! And not one time! ))
I just want to say that the explanation was not so detailed for beginners!
I made my own project with you idea wit a large set of data.
It seems that you variable vAndProduct should contain “+” operator instead “*”.
With vNotProduct seems everything OK!
Try to check this on a large set of data!
Regards, Boris.
Boris, the * operator is essential. + does something else entirely. In set analysis, * means intersection and + means union. Since we’re trying to create AND functionality, we specifically need all the points to intersect.