Skip to main content
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Filter Pivot Table with Button

I currently have a Pivot Table that is displaying the total number of records in different work queues, broken down by their current SLA status (Green, Yellow & Red).  Sample screenshot shown below:



What I'm trying to accomplish, if possible, would be to have a couple of Qlik buttons that could perform filtering of what is being displayed on the Pivot Table.  For example, Button 1 could filter out any Queues with zero records.  Button 2 would filter the pivot table to only display queues that have records in a Red SLA status.  Is this possible?  I've tried numerous things that I've found online but haven't found the right answer or example yet.  

Thanks for any assistance that can be given!

Labels (1)
4 Replies
Master II
Master II

to exclude resuls of aggregation, you would normally use set analysis.  so for example, to exclude grand total =0, you would use something like :


this would exclude the whole row Queue2

now if you want this controlled by a button, you can replace the set analysis by a variable:


then you create a button that replaces the value of the variable with your set expression:

value to set to = '{<Queue={"=aggr(sum(Measure),Queue)>0"}>}'

of course you may want another button that resets it to nothing but you should use a space:
= ' '

heres a sample:


the 1st button sets the variable:


the 2nd button resets the variable:


you can add other buttons that drive other criteria in your set analysis.

Contributor II
Contributor II

Thank you so much for your quick response and what looks exactly like what I'm looking for.  I'm a bit of a novice in Qlik Sense so excuse me for having to clarify, but where exactly should I be putting the set analysis expression?  Is this something that I add into my pivot table as a Dimension or Measure?  Appreciate the help!


Master II
Master II

you first have to know how to write set analysis.  and to answer your question - you add it into the measure, so for example:


$(vSACriteria) represents your set analysis expression

vSACriteria is a variable that you set with your set analysis expression

write your expression here, with the name of the field for Queue and i or someone else can help you write the set analysis for it

Contributor II
Contributor II

So here is the current state of my pivot table:


  • Field = [Queue Name]


  • Green: Count({<[SLA Status]={'Green'}>} Distinct [Record ID])
  • Yellow: Count({<[SLA Status]={'Yellow'}>} Distinct [Record ID])
  • Red: Count({<[SLA Status]={'Red'}>} Distinct [Record ID])
  • Total: Count(Distinct [Record ID])

This pivot table is being populated from a table of records that looks like this:



Hopefully this provides more information as to what I'm currently working with.  Thanks again for all of your time and assistance.