Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Accumulation

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!

OrderNoProductCodeDueOutDateQtyOrderedRUNNINGTOTAL
1

A

01/01/201511
2B02/01/201522
3A03/01/201534 (1+3)
3B03/01/201513 (2+1)
3C03/01/201522
4B04/01/201536 (2+1+3)
4C04/01/201513 (2+1)
5A05/01/201526 (1+3+2)
5C05/01/201536 (2+1+3)
5D05/01/201511
6B06/01/201528 (2+1+3+2)
6D06/01/201534 (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!!!

3 Replies
Highlighted
MVP
MVP

Have a look at:

The As-Of Table

Highlighted
MVP
MVP

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;


Capture.JPG

Highlighted

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

Capture.PNG