Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Running Total in a graph

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:

DateABCA+B-C
02/01/20164235
01/01/20161212

Please can you help?

Best regards,

Drew

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

9 Replies
sunny_talwar

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)

Not applicable
Author

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

sunny_talwar

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

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

Not applicable
Author

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

sunny_talwar

Check this out:

Table:

LOAD Date,

    A,

    B,

    C,

    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;


Capture.PNG

Not applicable
Author

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

sunny_talwar

One of these two expressions:

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

Capture.PNG

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

Capture.PNG

Not applicable
Author

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

sunny_talwar

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