topic Re: Running Total in a graph in QlikView

Running Total in a graph

Tue, 02 Feb 2016 11:20:40 GMT

Dear Community,

I'm looking to write a formula that references other cells to give me a "stock Total". It should add across the columns then pick up the total from the cell below (example below)

Table looks like below:

Date	A	B	C	A+B-C
02/01/2016	4	2	3	5
01/01/2016	1	2	1	2

Please can you help?

Best regards,

Drew

Re: Running Total in a graph

sunny_talwar — Tue, 02 Feb 2016 11:22:49 GMT

In the Script you can do this:

RangeSum(A, B, C) as Total

RangeSum(A, B, -C) as Total

or in the a chart you can do:

RangeSum(ExpA, ExpB, ExpC)

RangeSum(ExpA, ExpB, -ExpC)

Re: Running Total in a graph

Tue, 02 Feb 2016 12:33:48 GMT

Hi Sunny T,

I need to take into account the cell from below on the running total though, does that work? My example above add the 2 from the below cell.......

Re: Running Total in a graph

sunny_talwar — Tue, 02 Feb 2016 12:36:12 GMT

so the calculation is A + Cumulative(B) - C?

Is it time based accumulation for B? Like it resets each month or year?

Re: Running Total in a graph

Tue, 02 Feb 2016 12:39:12 GMT

it would be (A+B-C from 01/01/2016) + (A+B-c from 02/01/2016) and so on.....

No it would keep going and not reset. Essentially it's a stock level total. ABC are stock in and out each day but I need to know what the stock level would be after all those movements, so it needs to take into account what the level was from the day before.... hopefully that makes sense and thanks for your help

Re: Running Total in a graph

sunny_talwar — Tue, 02 Feb 2016 12:45:42 GMT

Check this out:

Table:

LOAD Date,

RangeSum(A,B,-C) as Temp

FROM

[https://community.qlik.com/thread/203848]

(html, codepage is 1252, embedded labels, table is @1);

FinalTable:

LOAD *,

RangeSum(Peek('RequiredColumn'), Temp) as RequiredColumn

Resident Table

Order By Date;

DROP Table Table;

Re: Running Total in a graph

Tue, 02 Feb 2016 12:50:24 GMT

This is excellent..... however I really need it to be in the chart (chart pulls from various tables) - I could rearrange the data but that will affect other information in the qvw

Re: Running Total in a graph

sunny_talwar — Tue, 02 Feb 2016 12:57:08 GMT

One of these two expressions:

1) RangeSum(A,B,-C) with Full Accumulation

2) =RangeSum(Above(RangeSum(A,B,-C), 0, RowNo()))

Re: Running Total in a graph

Tue, 02 Feb 2016 14:34:50 GMT

not sure what I'm missing? Resulting table expression for far right hand column

Re: Running Total in a graph

sunny_talwar — Tue, 02 Feb 2016 14:43:01 GMT

Won't be able to tell you much without looking at it, but try this:

=RangeSum(Above(TOTAL RangeSum(A,B,-C), 0, RowNo(TOTAL)))