topic Re: How to limit the rows displayed in a pivot table in QlikView

How to limit the rows displayed in a pivot table

Anonymous — Tue, 03 Feb 2015 13:44:01 GMT

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.

Re: How to limit the rows displayed in a pivot table

Tue, 03 Feb 2015 13:57:34 GMT

Hi Ed,

Can just use

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

In your expression

hope that helps

Joe

Re: How to limit the rows displayed in a pivot table

Anonymous — Tue, 03 Feb 2015 14:07:44 GMT

Joe,

That was too simple. Thanks for the help.

Re: How to limit the rows displayed in a pivot table

Anonymous — Tue, 03 Feb 2015 14:10:09 GMT

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

Re: How to limit the rows displayed in a pivot table

Anonymous — Tue, 03 Feb 2015 14:11:37 GMT

Joe ,

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

Re: How to limit the rows displayed in a pivot table

Tue, 03 Feb 2015 14:18:18 GMT

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

Joe

Re: How to limit the rows displayed in a pivot table

Anonymous — Tue, 03 Feb 2015 14:20:41 GMT

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)

Re: How to limit the rows displayed in a pivot table

Tue, 03 Feb 2015 14:35:40 GMT

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

Re: How to limit the rows displayed in a pivot table

Anonymous — Tue, 03 Feb 2015 14:45:38 GMT

THAT WORKS!

Thanks ,