Rangesum - Running Balance by Dimension

[#1]

I have looked at running total examples and am still lost on how to do.

We are evaluating Inventory balances by product by warehouse.

We want running totals for each change in product.

We have a record call TranType that gives us the beginning and ending balances.   We want to calc the running balance in between. 

Most of the examples i have seen use a date to control aggregation.  We dont care about date, our data is already rolling12 .

Attached is my excel using this formula for running

=IF(D6="BEG",E6,IF(D6="end","",F5+E6) )

[#2]

Thanks - that may be the compelling reason to upgrade.  I did actually take Nishant's and added/moved my columns around and it appears to be working.  Will look into the v12 aggr() - we haven't upgraded yet as I am waiting for direction on new infrastructure.

I will try yours here as well.  Thanks!

[Anonymous]

you could also sort in script, then add rowno() in my example ##  and use it as hidden dimension

[#4]

I was wrong - after I reloaded - and moved the transaction column over, it is not calculating correctly. and giving me the result i tested originally.  Will study the example posted and see if that works.  Will going to v12 solve this problem indefinitely?

[Anonymous]

My solution should be independent of column order (created it with QV11.2SR15),

because I "ignore" the dimensions by using above(total) in combination with aggr()...

QV12 sortable aggr() will help you only your row order is not sorted correctly...

[#6]

Here is my result with your method.  What am I missing?

Not calculating balances - Maybe the "Only" is causing issue?

[Anonymous · Accepted solution]

I was wrong, you don't need aggr() at all when using above( total...)

try this expression, (only(...) was just an example...)

if(WA_12mo.WA_TranType='BEG',
sum(WA_12mo.TotalCost),
if(WA_12mo.WA_TranType<>'END',
RangeSum(
sum(WA_12mo.TotalCost),
Above(TOTAL  RunBal) //recursive...
           )
)
)

[#8]

I also added TransAction date to the Dimensions in the formula and it looks like it is working -- will validate tomorrow and try this as well

[#9]

Is there a way to do this in a Pivot Table versus Straight Table

It looks much cleaner in  a Pivot, but you cannot hide the ## row

[Anonymous]

you don't need the "##" - row, if you guarantee the sorting...

just set every dimension the following way:

[#11]

Found a bug See attached Excel let me know if need to attach current QVW