topic Re: Rangesum - Running Balance by Dimension in QlikView

Rangesum - Running Balance by Dimension

Wed, 26 Apr 2017 21:12:46 GMT

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

Re: Rangesum - Running Balance by Dimension

Thu, 27 Apr 2017 23:42:34 GMT

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.

Re: Rangesum - Running Balance by Dimension

Fri, 28 Apr 2017 01:48:50 GMT

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

Re: Rangesum - Running Balance by Dimension

Fri, 28 Apr 2017 04:11:43 GMT

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

Re: Rangesum - Running Balance by Dimension

Fri, 28 Apr 2017 10:16:35 GMT

Hi Cam,

see attached.

Warm regards,

Nish

Re: Rangesum - Running Balance by Dimension

Fri, 28 Apr 2017 13:12:38 GMT

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

Re: Rangesum - Running Balance by Dimension

Fri, 28 Apr 2017 15:45:30 GMT

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?

Re: Rangesum - Running Balance by Dimension

Fri, 28 Apr 2017 15:50:31 GMT

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

Re: Rangesum - Running Balance by Dimension

Anonymous — Fri, 28 Apr 2017 15:56:30 GMT

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

Re: Rangesum - Running Balance by Dimension

Anonymous — Fri, 28 Apr 2017 17:32:25 GMT

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

Re: Rangesum - Running Balance by Dimension

Fri, 28 Apr 2017 17:39:59 GMT

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!

Re: Rangesum - Running Balance by Dimension

Anonymous — Fri, 28 Apr 2017 17:42:56 GMT

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

Re: Rangesum - Running Balance by Dimension

Fri, 28 Apr 2017 18:06:18 GMT

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?

Re: Rangesum - Running Balance by Dimension

Anonymous — Fri, 28 Apr 2017 18:10:02 GMT

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

Re: Rangesum - Running Balance by Dimension

Fri, 28 Apr 2017 19:17:46 GMT

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

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

Re: Rangesum - Running Balance by Dimension

Anonymous — Sat, 29 Apr 2017 08:28:09 GMT

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

Re: Rangesum - Running Balance by Dimension

Sun, 30 Apr 2017 22:57:10 GMT

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

Re: Rangesum - Running Balance by Dimension

Mon, 01 May 2017 15:37:24 GMT

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

Re: Rangesum - Running Balance by Dimension

Anonymous — Mon, 01 May 2017 15:44:13 GMT

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

just set every dimension the following way:

Re: Rangesum - Running Balance by Dimension

Tue, 02 May 2017 16:28:26 GMT

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