Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Community,
I'm looking to do a running total for the products ordered on the below table - the "RUNNINGTOTAL" column is where i need help in creating a formula. I would imagine something to do with Aggr, rangesum and above but i can't work it out!
OrderNo | ProductCode | DueOutDate | QtyOrdered | RUNNINGTOTAL |
---|---|---|---|---|
1 | A | 01/01/2015 | 1 | 1 |
2 | B | 02/01/2015 | 2 | 2 |
3 | A | 03/01/2015 | 3 | 4 (1+3) |
3 | B | 03/01/2015 | 1 | 3 (2+1) |
3 | C | 03/01/2015 | 2 | 2 |
4 | B | 04/01/2015 | 3 | 6 (2+1+3) |
4 | C | 04/01/2015 | 1 | 3 (2+1) |
5 | A | 05/01/2015 | 2 | 6 (1+3+2) |
5 | C | 05/01/2015 | 3 | 6 (2+1+3) |
5 | D | 05/01/2015 | 1 | 1 |
6 | B | 06/01/2015 | 2 | 8 (2+1+3+2) |
6 | D | 06/01/2015 | 3 | 4 (1+3) |
To summarise, I need an expression that will add up the orders for the productcode being represented on the line where the expression appears. For example, line 3 on the above table Adds the 3 ordered on line three to the 1 ordered on line one.
Please help!!!
Have a look at:
try below
Data:
LOAD OrderNo,
ProductCode,
DueOutDate,
QtyOrdered
FROM
[https://community.qlik.com/thread/194339]
(html, codepage is 1252, embedded labels, table is @1);
New:
NoConcatenate
LOAD *,
if(RowNo()=1 or ProductCode<>Previous(ProductCode),QtyOrdered,
if(ProductCode=Previous(ProductCode),rangesum(Peek('RUNNINGTOTAL'),QtyOrdered))) as RUNNINGTOTAL
Resident Data
Order by ProductCode,DueOutDate asc;
DROP Table Data;
For a front end solution, you will still need to correctly sort your data in the backend. Try this script:
Data:
LOAD * INLINE [
ON, PC, DOD, QO
1, A, 01/01/2015, 1
2, B, 02/01/2015, 2
3, A, 03/01/2015, 3
3, B, 03/01/2015, 1
3, C, 03/01/2015, 2
4, B, 04/01/2015, 3
4, C, 04/01/2015, 1
5, A, 05/01/2015, 2
5, C, 05/01/2015, 3
5, D, 05/01/2015, 1
6, B, 06/01/2015, 2
6, D, 06/01/2015, 3
];
New:
LOAD RowNo() as SNo,
ON as OrderNo,
PC as ProductCode,
DOD as DueOutDate,
QO as QtyOrdered
Resident Data
Order by PC, DOD;
DROP Table Data;
Once you have sorted the data correctly in the script, you should be able to use this expression:
Aggr(RangeSum(Above(QtyOrdered, 0, RowNo())), ProductCode, SNo)
Output