topic Re: Eliminate pivot table rows where expression is 0 (zero) in QlikView

Eliminate pivot table rows where expression is 0 (zero)

Fri, 20 May 2016 09:31:45 GMT

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.

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

Thanks Matt

Re: Eliminate pivot table rows where expression is 0 (zero)

tamilarasu — Fri, 20 May 2016 09:47:30 GMT

Hi Matt,

Check the attachment.

Re: Eliminate pivot table rows where expression is 0 (zero)

swuehl — Fri, 20 May 2016 09:47:50 GMT

You could set all other expressions in a condition like

=If("Quantity Backordered",

TheOtherExpression

)

Re: Eliminate pivot table rows where expression is 0 (zero)

Fri, 20 May 2016 12:27:18 GMT

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:

Re: Eliminate pivot table rows where expression is 0 (zero)

Fri, 20 May 2016 12:29:19 GMT

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.

Re: Eliminate pivot table rows where expression is 0 (zero)

Fri, 20 May 2016 12:45:41 GMT

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

Re: Eliminate pivot table rows where expression is 0 (zero)

tamilarasu — Fri, 20 May 2016 14:03:42 GMT

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

Re: Eliminate pivot table rows where expression is 0 (zero)

Fri, 20 May 2016 14:42:50 GMT

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

Re: Eliminate pivot table rows where expression is 0 (zero)

tamilarasu — Fri, 20 May 2016 14:49:08 GMT

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.

Re: Eliminate pivot table rows where expression is 0 (zero)

tamilarasu — Fri, 20 May 2016 15:04:19 GMT

Matt,

Try like below,

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

Re: Eliminate pivot table rows where expression is 0 (zero)

swuehl — Fri, 20 May 2016 15:04:48 GMT

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

Re: Eliminate pivot table rows where expression is 0 (zero)

Sun, 22 May 2016 09:56:19 GMT

Thanks swuehl! This works perfectly after making some changes the the dimensions of the aggre function!!