topic How to filter the data inside pivot table? in QlikView

How to filter the data inside pivot table?

Mon, 03 Mar 2014 20:15:19 GMT

Hi Community,

I am using Pivot table chart to show the summarized data of infection data. However, there are huge number of cells, rows and columns are empty - there are no data. Is there any way I can filter out these empty rows or columns? I just want to show the rows or columns with data. One step further, is this possible to filter out the rows and colums whose maximum data in the cells is equal to a given number? For example, I only want to show the rows or columns whose maximum number (count of records) is equal to 5?

Thanks

Longmatch

Re: How to filter the data inside pivot table?

chiru_thota — Mon, 03 Mar 2014 20:23:01 GMT

You can use below for first requirement

(2) For second requirement you can use input filed and set analysis by using that value in expressions.

Re: How to filter the data inside pivot table?

Anonymous — Mon, 03 Mar 2014 20:37:33 GMT

Hi Haijun,

Query 1:

In Pivot table -

Layout tab - Conditional show text box

You can hide/show an object by using "Conditional" on Layout tab.

In the Conditional, use any of the expression from your table and make them non zero.

For Ex: Amount column

Sum(Amount) <> 0

or Dimension tab - select suppress when value is null

Query 2:

To show the rows or columns whose maximum number (count of records) is equal to 5

Expression tab - expression

Count({<Dept={"=rank(Count(CustomerID))<=5"}>}CustomerID)

Re: How to filter the data inside pivot table?

Mon, 03 Mar 2014 21:44:47 GMT

Hi Nitha, It seems your solution works, but I am not clear about what will be displayed when the number is to 5. Top 5 department whose customer numbers are over 5? If there are multiple departments, how does this work? Thanks

Re: How to filter the data inside pivot table?

Anonymous — Tue, 04 Mar 2014 09:45:42 GMT

Hi Haijun,

Please can you explain with sample file,what do you expect?

when you select multiple departments in listbox,it should select top 5 customers.

for top five departments,Try to find rank of Department as well same like CustomerID