Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Rangesum - Running Balance by Dimension

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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

View solution in original post

22 Replies
Not applicable
Author

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.

WhseProductTransactionTypeAmountBalance
1

A

BEG100

100 = Amt if type = BEG

1AInvoice-5050
1AReceipt100150
1AEND150

- (do not add if Type = END)

1BBeg300300
1BInvoice-3000
1

B

Ending0-
1CBeg500500
1CInvoice-200300
1CReceipt100400
1CEnding400

-

2CBeg600600
2CAdjustment200800
2CInvoice-500300
2CTransfer-3000
2CEnding0-

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.

Not applicable
Author

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.

Capture.GIF

Warm regards,

Nish

Not applicable
Author

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

Not applicable
Author

Hi Cam,

see attached.

Warm regards,

Nish

Not applicable
Author

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

Not applicable
Author

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?

Not applicable
Author

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
Not applicable
Author

Anonymous
Not applicable
Author

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