Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

mikegrattan
Contributor II

How to filter pivot table on aggregate total of a measure?

I have a pivot table that shows rows for employees and columns for years; the measure is "sum([number of incidents])".  I'd like to be able to filter the table so it shows only employees with a number of incidents greater than <a number selected by the user>.  It would be great, for example, if I could embed a slider in the Sheet with numbers from 1-10 and let the user pick a number; then the pivot table is filtered to show only employees who had at least that number of incidents.  In the screen grab below you can see that we have multiple years in the table.  I would want to first have a total for all years, and then be able to filter by that total.

10.28.2015-14.31.png

1 Reply
mikegrattan
Contributor II

Re: How to filter pivot table on aggregate total of a measure?

I figured out a workable solution.

In the pivot table, I turned on "Show Totals" in the Year column in the Data section.  In the Sorting section, I applied a Descending sort order to the Employee row, based on the sum of Incident Count.  I then added some conditional formatting to the Incident Count in the Background color expression formula:

if( sum(INCIDENTCOUNT) < 2, Green(),if( sum(INCIDENTCOUNT) < 5 ,Yellow(), if(sum(INCIDENTCOUNT) >=5, Red())))

This resulted in the following pivot table appearance:

12.03.2015-10.53.png