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]

OK since no one has an answer, please Google Cumulative Sum, Running Totals, Aggregation in Qlikview and help me find an example where it shows a running total that changes/resets everytime it sees a value in one of the dimensions (or 2)

I seriously find it hard to believe this has not or cannot be done.

Whse	Product	TransactionType	Amount	Balance
1	A	BEG	100	100 = Amt if type = BEG
1	A	Invoice	-50	50
1	A	Receipt	100	150
1	A	END	150	- (do not add if Type = END)
1	B	Beg	300	300
1	B	Invoice	-300	0
1	B	Ending	0	-
1	C	Beg	500	500
1	C	Invoice	-200	300
1	C	Receipt	100	400
1	C	Ending	400	-
2	C	Beg	600	600
2	C	Adjustment	200	800
2	C	Invoice	-500	300
2	C	Transfer	-300	0
2	C	Ending	0	-

I have used Aggr and rangesum to no success.   If I say =aggr(sum(TotalAmt), Whse, Product) I will get a correct total, but only on the BEG balance line of each product . 

Need to strip down a version to post as our application is quite large.

[#3]

Hi Cam,

Is the qvw attached any good? Balance is cumulative using peek script function which resets when it sees different Product and does not add when Type = End/Ending, screenshot below.

Warm regards,

Nish

[#4]

Thanks for the reply Nish! - Here is a QVW of what I am trying to do.

I have a "self-serve" type of page.  We would most likely assess running totals at the Warehouse and the Product level (put conditional on expression column )

While it looks like your code does what I want, I am trying to do in a pivot table

[#5]

Hi Cam,

see attached.

Warm regards,

Nish

[#6]

Hi Cam,

If my replies help you achieve what you want, I would appreciate it if you could show your appreciation by marking them as correct/helpful - this is deemed as a good practice by Qlik Community

Many thanks

Nish

[#7]

OK - I was using the right formula at one point, but based on your example, is the only way to get it right? to drop the other dimensions and change the column order?

[#8]

THe other problem is by moving the Sort order of the columns, the Items get mucked up so you have all the beginning balances for all the items pulled to the top and not a contiguous flow of 1 item and all its transactions.

But yes this gets closer

[Anonymous]

Running totals per dimension (RangeSum(), sortable Aggr()...)

[Anonymous]

works only, if rows are sorted "correct", else you have to use QlikView 12 + sortable aggr()

--> the solution is independent of column order

LOAD RowNo() as ##,*;
LOAD * INLINE [
Whse, Product, TransactionType, Amount
1, A, BEG, 100
1, A, Invoice, -50
1, A, Receipt, 100
1, A, END, 150
1, B, BEG, 300
1, B, Invoice, -300
1, B, END, 0
1, C, BEG, 500
1, C, Invoice, -200
1, C, Receipt, 100
1, C, END, 400
2, C, BEG, 600
2, C, Adjustment, 200
2, C, Invoice, -500
2, C, Transfer, -300
2, C, END, 0
];

yourexp:

if(TransactionType='BEG',
aggr(Only(Amount),Whse,Product, TransactionType,Amount),
if(TransactionType<>'END',
RangeSum(
aggr(Only(Amount),Whse,Product, TransactionType,Amount),
Above(TOTAL  yourexp)  //recursive...
                  )
)
)