topic Re: Filter Pivot Table with Button in App Development

Filter Pivot Table with Button

trevorcrow — Tue, 07 May 2024 21:18:52 GMT

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!

Re: Filter Pivot Table with Button

edwin — Tue, 07 May 2024 21:48:56 GMT

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

Sum({<Queue={"=aggr(sum(Measure),Queue)>0"}>}Measure)

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:

Sum($(vSACrietria)Measure)

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.

Re: Filter Pivot Table with Button

trevorcrow — Wed, 08 May 2024 18:31:48 GMT

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!

Re: Filter Pivot Table with Button

edwin — Wed, 08 May 2024 19:54:31 GMT

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

Sum($(vSACrietria)Measure)

$(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

Re: Filter Pivot Table with Button

trevorcrow — Wed, 08 May 2024 21:33:22 GMT

So here is the current state of my pivot table:

Dimensions

Field = [Queue Name]

Measures

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.