topic Re: How to filter out data interactively based on values of more than one field in QlikView

How to filter out data interactively based on values of more than one field

Lumino — Sat, 16 Nov 2024 20:45:59 GMT

Hi,

I have a requirement to allow the user to do relatively complex filtering on a pivot table report, in particular excluding data based on the value of multiple fields.

E.g. in SQL terms, where A, B and C are fields:

A="1" AND NOT(B="2" and C="3")

Filtering on field A is easy using either a search object or a list box.

I can filter on NOT(B="2") in the same way (using Select Excluded on a search object or a list box).

But how can a user filter out data where B has some value AND C has some value?

I understand I could do this by modifying the measure (using a set expression), but users need to be able to modify these filters on the fly (hence the reason we can't apply the filter in the load script).

Any thoughts greatly appreciated.

Liam

Re: How to filter out data interactively based on values of more than one field

jwjackso — Wed, 29 May 2019 18:08:22 GMT

You could have 3 list boxes, 1 for each field. Create a variable and use triggers to dynamically build a set analysis statement that is assigned to the variable.

Sum($(vSetAnalysisVariable) Amt)

Re: How to filter out data interactively based on values of more than one field

rwunderlich — Wed, 29 May 2019 19:25:43 GMT

It seems to me that you could make these selections as is in listboxes. If your concern is subsequent selections may clear incompatible previous selections, you can lock the selections as you go along.

-Rob

Re: How to filter out data interactively based on values of more than one field

Lumino — Wed, 29 May 2019 19:26:51 GMT

Thanks Jwjackso that's interesting and could work if users always want to filter on

A=<selected A> AND NOT (B=<selected B> AND C=<selected C>)

... but I'm really searching for a more general solution. Another user might want to do

NOT(A="1" and B="2") AND C="3"

Liam

Re: How to filter out data interactively based on values of more than one field

Lumino — Wed, 29 May 2019 19:34:23 GMT

Hi Rob, how can you specify NOT(B="2" AND C="3") using listboxes?

If I do

- Select 2 in listbox for B and then choose "Select Excluded"

- Select 3 in listbox for C and the choose "Select Excluded"

I have specified NOT(B="2") AND NOT(C="3")

Am I missing something?

Re: How to filter out data interactively based on values of more than one field

jwjackso — Wed, 29 May 2019 20:04:09 GMT

This is a screen shot of a proof of concept that I did using alternate states to build a set analysis statement that I store in a variable.. The blue text box displays the set analysis statement that I build. It is actually in use because the users wanted to create filters where they could test if an event happened in 2017 for item 1 and happened in 2018 for item 2. The normal qlik filter would return true if item 1 or item 2 happened in 2017 or 2018. The buttons toggle between And and Or. I toggle the parentheses on/off to control the evaluation of the logical operators.

Re: How to filter out data interactively based on values of more than one field

rwunderlich — Wed, 29 May 2019 20:59:35 GMT

No, it was me that was missing something. Like careful reading 🙂

-Rob

Re: How to filter out data interactively based on values of more than one field

Lumino — Thu, 30 May 2019 10:45:18 GMT

Thanks Jwjackso, appreciate your time. I believe your general point is that one could create an interface for users to build up a complex filter expression that's stored as a variable and applied to the measure. In particular I think you've rightly identified the parenthesis as being the element "missing" from the standard tools (list box and search object) in order to be able to do what I want.

I am going to think about whether we could build something (e.g. maybe an SQL->set expression tool).

One thing I'm not clear on, what does the expression look like for A="1" AND NOT (B="2" AND "C="3")?

Liam