topic Re: Accumulation in QlikView

Accumulation

Mon, 07 Dec 2015 11:22:46 GMT

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

Re: Accumulation

swuehl — Mon, 07 Dec 2015 11:26:51 GMT

Have a look at:

The As-Of Table

Re: Accumulation

Kushal_Chawda — Mon, 07 Dec 2015 11:52:27 GMT

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;

Re: Accumulation

sunny_talwar — Mon, 07 Dec 2015 13:23:52 GMT

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