Cross Filtering Clients Based on Whether They Have or Do Not Have Certain Products

[ozz1k]

Hi Qlik Community,

I'm seeking assistance in building a couple of less intuitive filters based on my simple data set. I have two tables linked together: Clients and Products. One client can own zero to many products and vice versa.

Now, I'm trying to build filters at the fronted so that my users can eaily find the following:

a) Clients that own Product A AND Product B AND Product C AND ... (it should be easy to select any combination of products and get clients (if any) that own all of them).

b) Clients that do not own Product X AND Product Y AND Product Z AND ... (it should be easy to select any combinations of products and get clients (if any) that do not own any of them)

c) A combination of the two filters above, i.e. find clients that own Product A AND Product B but do not own Product X AND Product Y

I can manipulate my data however is required at the beackend but the resulting filters should be pretty intuitive for end users. Appreciate your ideas!

[ozz1k · Accepted solution]

I found a solution in the post: https://community.qlik.com/t5/New-to-QlikView/How-Do-I-Filter-for-the-Non-Existence-of-Child-Records...

For scenario (a), you select product A and use 'Select possible' filter on Client ID/Name to narrow down to clients that own the selected product. You can then unselect A and pick product B and it will show you clients that own A and B. Repeat if you need to add more products to the rule.

For (b), you simply select all products to exclude and use 'Select excluded' filter on Client ID/Name to narrow down to clients that do not own any of the selected products.

(c) is simply a mix of (a) and (b). 

Not the cleanest solution possible, but it certainly does the trick without much manipulation with the data on the backend. All hail the associative engine.