topic Re: Using subtraction with a running total in QlikView

Using subtraction with a running total

mellerbeck — Fri, 09 Sep 2011 17:31:40 GMT

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

Re: Using subtraction with a running total

spividori — Fri, 09 Sep 2011 19:41:07 GMT

Hi.

See the example.

Hope this help!

Regards.

Using subtraction with a running total

mellerbeck — Mon, 12 Sep 2011 18:10:03 GMT

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!

Using subtraction with a running total

johnw — Mon, 12 Sep 2011 20:08:55 GMT

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.

Using subtraction with a running total

spividori — Mon, 12 Sep 2011 20:10:33 GMT

Hi.

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

Regards.

Re: Using subtraction with a running total

mellerbeck — Mon, 12 Sep 2011 20:25:53 GMT

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

Message was edited by: mellerbeck

Re: Using subtraction with a running total

johnw — Mon, 12 Sep 2011 20:36:22 GMT

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

Re: Using subtraction with a running total

mellerbeck — Mon, 12 Sep 2011 20:41:27 GMT

Thank you!

Using subtraction with a running total

johnw — Mon, 12 Sep 2011 20:44:47 GMT

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.

Re: Using subtraction with a running total

mellerbeck — Mon, 12 Sep 2011 20:47:44 GMT

Oh, right. Done

Re: Using subtraction with a running total

spividori — Mon, 12 Sep 2011 20:55:43 GMT

No problem, we all help each other. Thank you.

Regards.