Accumulation

[#1]

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

[swuehl]

Have a look at:

The As-Of Table

[Kushal_Chawda]

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;

[sunny_talwar]

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