Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Eliminate pivot table rows where expression is 0 (zero)

Hi All,


I have the following pivot table. Is there a way how I can eliminate rows from the pivot table where the "Quantity Backordered" expression is equal to 0? I want to achieve this while still getting the correct Sum values for the pivot table.

Capture.PNG

I also attached the QVW to give you access to the pivot table I am talking about.

Thanks Matt

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe like this, I've only changed the first expression, apply to the other expression columns except "Quantity Backordered" accordingly.

View solution in original post

11 Replies
tamilarasu
Champion
Champion

Hi Matt,

Check the attachment.

swuehl
MVP
MVP

You could set all other expressions in a condition like

=If("Quantity Backordered",

TheOtherExpression

)

Not applicable
Author

Hi Tamil, Thanks for you help.

However I already tried this. The problem with this is that although it still gives the SUM, the sum also includes those records where the 'Quantity Backordered' value is 0, thus is incorrect:

Untitled.png

Not applicable
Author

Hi swuehl,

Thanks for you help, however for the same reason I gave to Tamil above, this solution does not return the desired SUM values.

Not applicable
Author

I tried doing the same 'if' condition inside the SUM function but it does not give eliminate any rows

tamilarasu
Champion
Champion

Hi Matt,

Sorry,I couldn't open your application now. You should use Aggr in order to show the exact total. Try like below for all expression

Sum(Aggr(Expression1, Dimension1, Dimension2....))


Sum(Aggr(Expression2, Dimension1, Dimension2....))


Not applicable
Author

It still did work for me here is my QVW if you want to give it a shot! ! !

tamilarasu
Champion
Champion

Now, I am replying from my mobile and came out from my office. If you could post your expression and what you have tried, I can get an idea. Or someone might helps you.

tamilarasu
Champion
Champion

Matt,

Try like below,

If([Quantity Backordered]>0, Sum(Aggr(YourExp, Customer, [Part #])))