topic Re: Suppress pivot table row if expression is zero in QlikView

Suppress pivot table row if expression is zero

sifatnabil — Tue, 13 Mar 2018 14:45:18 GMT

Hi all,

I have a pivot table with 2 dimensions, Customer and Product Group, and I only want to show rows where sum(Sales)>0 for both ProductA and ProductB. So in the below example, only the highlighted rows should be shown because there are non-zero values in both products. How can I suppress the others?

Customer	Product Group	ProductA	ProductB
A		84	568
B		456	-
X		-	76
D		-	567
G		34	4
R		-	3
S		-	567
I		-	567
L		10,999	2,003,111
P		644,929	854,950

Re: Suppress pivot table row if expression is zero

YoussefBelloum — Tue, 13 Mar 2018 14:48:29 GMT

Hi,

try this:

Sum( {<Customer={"=sum(Field)>0"}>} Field)

Re: Suppress pivot table row if expression is zero

sifatnabil — Tue, 13 Mar 2018 14:55:47 GMT

Hi, this still shows all the rows.

Re: Suppress pivot table row if expression is zero

YoussefBelloum — Tue, 13 Mar 2018 15:07:12 GMT

did you replace Field with your Measure field name ?

Can you attach some rows of your data or sample data ?

Re: Suppress pivot table row if expression is zero

sifatnabil — Tue, 13 Mar 2018 15:16:58 GMT

Yes I did. Here's the raw data:

Customer	Product Group	Sales
A	ProductA	84
B	ProductA	456
X	ProductA	-
D	ProductA	-
G	ProductA	34
R	ProductA	-
S	ProductA	-
I	ProductA	-
L	ProductA	10,999
P	ProductA	644,929
A	ProductB	568
B	ProductB	-
X	ProductB	76
D	ProductB	567
G	ProductB	4
R	ProductB	3
S	ProductB	567
I	ProductB	567
L	ProductB	2,003,111
P	ProductB	854,950

Re: Suppress pivot table row if expression is zero

MK_QSL — Tue, 13 Mar 2018 15:18:11 GMT

Use calculated dimension for Product

Dimension

=Aggr(IF(Aggr(SUM(Sales),Customer) <> Aggr(SUM(Sales),Customer,[Product Group]),Customer),Customer,[Product Group])

Tick Suppress When Value is Null

2) [Product Group]

Expression

SUM(Sales)

NOTE : This will work only if there are only two Products in Product Group.

Re: Suppress pivot table row if expression is zero

MK_QSL — Tue, 13 Mar 2018 16:00:41 GMT

You can use below.. This should work for any number of Product in Product Group.

Dimension

Customer

Product Group

Expression

IF(SUM(TOTAL <Customer> Aggr(IF(SUM(Sales)>0,1,0),Customer,[Product Group])) = Count(Total Distinct [Product Group]),SUM(Sales))

Re: Suppress pivot table row if expression is zero

YoussefBelloum — Tue, 13 Mar 2018 16:04:58 GMT

excellent