How to filter the data inside pivot table?

[#1]

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

[chiru_thota]

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.

[Anonymous]

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)

[#4]

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

[Anonymous]

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