Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
sifatnabil
Specialist
Specialist

Suppress pivot table row if expression is zero

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?

   

CustomerProduct GroupProductAProductB
A 84568
B 456-
X -76
D -567
G 344
R -3
S -567
I -567
L 10,9992,003,111
P 644,929854,950
1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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))

View solution in original post

7 Replies
YoussefBelloum
Champion
Champion

Hi,

try this:

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

sifatnabil
Specialist
Specialist
Author

Hi, this still shows all the rows.

YoussefBelloum
Champion
Champion

did you replace Field with your Measure field name ?

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

sifatnabil
Specialist
Specialist
Author

Yes I did. Here's the raw data:

   

CustomerProduct GroupSales
AProductA84
BProductA456
XProductA-
DProductA-
GProductA34
RProductA-
SProductA-
IProductA-
LProductA10,999
PProductA644,929
AProductB568
BProductB-
XProductB76
DProductB567
GProductB4
RProductB3
SProductB567
IProductB567
LProductB2,003,111
PProductB854,950
MK_QSL
MVP
MVP

Use calculated dimension for Product

Dimension

1)

=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.

MK_QSL
MVP
MVP

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))

YoussefBelloum
Champion
Champion

excellent