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

[Lumino]

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

[jwjackso]

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)

[rwunderlich]

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

[Lumino]

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

[Lumino]

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?

[jwjackso]

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.

[rwunderlich]

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

-Rob

[Lumino]

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