Discussion Board for collaboration related to QlikView App Development.
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
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)))
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)
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.......
so the calculation is A + Cumulative(B) - C?
Is it time based accumulation for B? Like it resets each month or year?
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
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;
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
One of these two expressions:
1) RangeSum(A,B,-C) with Full Accumulation
2) =RangeSum(Above(RangeSum(A,B,-C), 0, RowNo()))
not sure what I'm missing? Resulting table expression for far right hand column
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)))