QlikSense: Using an OR in filter pane

Question

Is there a way in QlikSense to use a single filter value to search across multiple objects?

So in my example above, I want to be able to select one value, but have it look at every object. These rows are all correlated, as well. Here's an example of the SQL data:

The most straight forward path would be to have 3 separate filters for the Drug (Drug1, Drug2 and Drug3) but I'd prefer to have one filter search across all 3 of these. Hopefully this is a clear enough explanation for what I'm trying to do.

I tried using an expression in the filter pane: = drug1 OR drug2 OR drug3 but this didn't work.

Answer 1

You can achieve this by using a variable, a variable input object (textbox), and some set analysis.

1. Create a new empty variable in the sheet editor called vSearchString.
2. Add a new variable input object to the sheet, make it a textbox, and then choose the vSearchString variable.
3. In your table object, use this as your drug1 dimension:

->

=Aggr(Only({
    <[drug1] = P({< [drug1]={"*$(vSearchString)*"} >})>
  + <[drug1] = P({< [drug2]={"*$(vSearchString)*"} >})>
  + <[drug1] = P({< [drug3]={"*$(vSearchString)*"} >})>
  } [drug1]), [drug1])

...Then, use this as your drug2 dimension:

=Aggr(Only({
    <[drug2] = P({< [drug1]={"*$(vSearchString)*"} >})>
  + <[drug2] = P({< [drug2]={"*$(vSearchString)*"} >})>
  + <[drug2] = P({< [drug3]={"*$(vSearchString)*"} >})>
  } [drug2]), [drug2])

...And finally, use this as your drug3 dimension:

=Aggr(Only({
    <[drug3] = P({< [drug1]={"*$(vSearchString)*"} >})>
  + <[drug3] = P({< [drug2]={"*$(vSearchString)*"} >})>
  + <[drug3] = P({< [drug3]={"*$(vSearchString)*"} >})>
  } [drug3]), [drug3])

That should be enough to get it working:

Those Qlik expressions shown above utilize a few different concepts:

• Aggr() function
  • This function is sort of like a GROUP BY clause.
  • You give it an aggregation and then any number of dimension fields and it will return the aggregated values grouped on those dimensions.
  • It looks like this --> =Aggr(Sum([Profit]), [RegionDim], [SalespersonDim]).
  • We use it because we want to use the Only() aggregation (see next bullet point) to use set analysis (see bullet point after next).
• Only() function
  • This is an aggregation function that will return a value only if there is one value to return.
  • We want to use it because we can only use set analysis inside aggregation functions. We'll use set analysis (see below) to search for our text inside the field values and then we use the Aggr() function (see above) to group those results by that same field ([drug1]/[drug2]/[drug3]).
• Set analysis
  • This is a syntactical way of building in selections into an aggregation.
  • An overly simplified way of looking at set analysis is that it's like a SQL WHERE clause for chart expressions in Qlik.
  • The syntax looks like this --> {<[SomeField] = {'Some value or expression'}>}.
• P() set function
  • This set function can only be used in set analysis. The P stands for "possible."
  • This function basically means "return all possible values where [SomeField] = 'Some value'".
• Union (+) set operator
  • This is basically an AND used to combine several "sets" or set analysis records.