accumulate values by a certain column

[yacine]

hello everyone,

i want to sum values and accumulate them depending on a certain dimension.

i have this table:

Quarter	Depart	Prod
Q1	A	10
Q1	A	22
Q1	B	61
Q2	A	651
Q2	B	651
Q2	B	8
Q3	A	51
Q3	B	84
Q3	A	654
Q4	A	132
Q4	B	321
Q4	A	15

and i want to sum the Prod by Quarter and Depart, amd then accumulate the Prod values (as show in the table below):

Quarter	Depart	Prod	Accum. Prod
Q1	A	32	32
Q2	A	651	683
Q3	A	705	1388
Q4	A	15	1403
Q1	B	61	61
Q2	B	659	720
Q3	B	84	804
Q4	B	321	1125

thank you in advance.

[SchalkF]

Hi,

For your first question:

You can use aggr() to accomplish this (For your dataset, since it is small and simple, if you just do a Sum(Prod), it should already aggregate on Quarter,Depart):

Aggr(Sum(Prod), Quarter, Depart)

I just change the sorting to sort on Depart first and then on Quarter:

For you second requirement please have a look at the below thread:

https://community.qlik.com/t5/App-Development/Accumulation-in-Qlik-Sense/td-p/670706

rangesum(above(sum(Prod),0,rowno()))

Kind regards

[Ruhulessin · Accepted solution]

Hi @yacine,

There is also a possibility to calculate this in script, by creating the right order and using Previous() and Peek().

PFA my example app which follows this script:

t1:
LOAD * INLINE [
Quarter, Depart, Prod
Q1, A, 10
Q1, A, 22
Q1, B, 61
Q2, A, 651
Q2, B, 651
Q2, B, 8
Q3, A, 51
Q3, B, 84
Q3, A, 654
Q4, A, 132
Q4, B, 321
Q4, A, 15
];

NOCONCATENATE
t2:
LOAD
Quarter
, Depart
, Sum(Prod) AS Prod
RESIDENT t1
GROUP BY
Quarter
, Depart
;
DROP TABLE t1
;

NOCONCATENATE
t3:
LOAD *
, If(Previous(Depart) = Depart
, Peek(ProdAcc, -1) + Prod
, Prod
) AS ProdAcc
;
LOAD *
RESIDENT t2
ORDER BY
Depart, Quarter Asc
;
DROP TABLE t2
;

-Ruben

[yacine]

thank you very much @Ruhulessin