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
I was wrong, you don't need aggr() at all when using above( total...)
try this expression, (only(...) was just an example...)
Above(TOTAL RunBal) //recursive...
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.
100 = Amt if type = BEG
- (do not add if Type = END)
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.
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.
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
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
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
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
Above(TOTAL yourexp) //recursive...