Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
mellerbeck
Contributor II

Using subtraction with a running total

So I have a pivot table

Quantity Limit        PO #     Received Qty Totals     Balance Remaining

333,492                    1          46,669                        286,823

                               2          126,673                       160,150

                               3          46,669                         113,981

And I'm try to calculte the Balance Remaining Which is basically the running total of Quantity Limit - Received Qty Totals

I've tried

RangeSum(-[Received Qty Totals], Above([Balance Remaining]))

But so far no dice, any help would be much appreciated.

Thanks,

Michael

1 Solution

Accepted Solutions
Highlighted
spividori
Valued Contributor

Re: Using subtraction with a running total

Hi.

See the example.

Hope this help!

Regards.

View solution in original post

10 Replies
Highlighted
spividori
Valued Contributor

Re: Using subtraction with a running total

Hi.

See the example.

Hope this help!

Regards.

View solution in original post

Highlighted
mellerbeck
Contributor II

Using subtraction with a running total

Hi Sandro, this is exactly what I want! Only problem now is that the total line (using Show Partial Sums) looks like it is a null now (or doesn't work). Reading from

(John Witherspoon)

A pivot table, and also straight tables by default, reevaluate your expression for the total row instead of doing a sum of rows. A straight table lets you override this default behavior directly and tell it you want a sum of rows. A pivot table doesn't let you do that. So you have to build your "sum of rows" manually using sum(aggr()). Like this, putting in your dimensions instead of what I listed:

sum(aggr(RangeMax(0, sum((FORMPERCENTAGE * Quantity)/100)),YourFirstDimension,YourSecondDimension,etc.))

I can sort of grasp the concept that I need to Total it manually, but can't quite figure it out. Any thoughts?

Thanks!    

Highlighted
MVP
MVP

Using subtraction with a running total

It's not null for me.  If I tell it to give me a partial sum for POs, I get 220011 and 113481, which make sense to me.

Highlighted
spividori
Valued Contributor

Using subtraction with a running total

Hi.

I had used a few instances in which John mentioned. You could post an example to try to solve it?

Regards.

Highlighted
mellerbeck
Contributor II

Re: Using subtraction with a running total

Here is what it looks like, it probably is something obvious.

Message was edited by: mellerbeck

Highlighted
MVP
MVP

Re: Using subtraction with a running total

Here's how I interpret Sandro's solution.  Difference in red.  The partial sums line then has a total.

if(IsNull(Above([Balance Remaining])),[Quantity Limit]-[Received Qty Totals],above([Balance Remaining])-[Received Qty Totals])

Highlighted
mellerbeck
Contributor II

Re: Using subtraction with a running total

Thank you!

Highlighted
MVP
MVP

Using subtraction with a running total

Can you give Sandro the credit for the solution?  All I did was clarify how to interpret his solution for your specific field and column names.

Highlighted
mellerbeck
Contributor II

Re: Using subtraction with a running total

Oh, right. Done