How to limit the rows displayed in a pivot table

[Anonymous]

I have a pivot table that shows customer number, customer name and number of distinct customers.  I only want to show the rows where the number of distinct customers is greater than 1.  See attached qlikview file.

How can I only show the rows where the Nbr of Customers is > 1 ?

Thanks.

Ed

[#2 · Accepted solution]

Hi Ed,

Can just use

IF(COUNT(DISTINCT(Cust_Name))>1,
COUNT(DISTINCT(Cust_Name)))

In your expression

hope that helps

Joe

[Anonymous]

Joe,

That was too simple.  Thanks for the help.

Ed

[Anonymous]

Hi

As you are using Cust_Name in dimension once expand the customer nmbr ..above solution will not work .

you need to use below expression

if(aggr(COUNT(DISTINCT(Cust_Name)),Cust_nbr)>1,COUNT(DISTINCT(Cust_Name)))

PFA

[Anonymous]

Joe ,

Can you attach the application if it is working as expected.

[#6]

Good point, I didn't spot the extra dimension in there, you're right mine wouldn't work with that in there

Joe

[Anonymous]

Your right, Joe's answer does not work.  Unfortunately neither does yours.  Yes it displays the number counts if the total is > 1 BUT if you expand the pivot table you only see 1 Cust_Name value of A (you should see A,B and C)

[#8]

Just change it to

If(aggr(NODISTINCT COUNT(DISTINCT(Cust_Name)),Cust_nbr)>1,COUNT(DISTINCT(Cust_Name)))

you need the nodistinct in the aggr function to ensure you get all three rows

hope that helps

Joe

[Anonymous]

THAT WORKS!

Thanks ,

Ed