topic Re: SUM WITH PREVIOUS ROW FOR EACH NAME in QlikView

SUM WITH PREVIOUS ROW FOR EACH NAME

entsh — Thu, 13 May 2021 05:52:42 GMT

please see the table 1

ROW	NAME	NUMBER	1-SUM (SUM WITH PREVIOUS ROW FOR EACH NAME)FROM SMALLEST ROW TO LARGEST ROW NUMBER
1	A	500	=500
4	A	450	450+500=950
8	A	550	950+550=1500
10	A	1000	1500+1000=2500
15	A	500	2500+500=3000
3	B	400	=400
5	B	600	=400+600=1000
42	B	700	1000+700=1700

RESULT

NAME	RESULT (TOTAL SUM)
A	3000
B	1700

I WANT THE FORMULA FOR 1-SUM COLUMN AND 2-RESULT TABLE

THX

Re: SUM WITH PREVIOUS ROW FOR EACH NAME

mato32188 — Thu, 13 May 2021 06:47:40 GMT

Hi @entsh ,

if you have not sorted your table yet, please do the following in script:

tmp:

Load

ROW,

NAME,

NUMBER

from xxx;

noconcatenate

tmp1:

load * resident tmp order by NAME; drop table tmp;

tmp2:

load

ROW,

NAME,

NUMBER,

if(previous(NAME)=NAME, rangesum(NUMBER,peek('NUMBER_ACC')),NUMBER) as NUMBER_ACC

Resident tmp1;

drop table tmp1;

Then you can simply sum the NUMBER_ACC in any object you want.

Re: SUM WITH PREVIOUS ROW FOR EACH NAME

Saravanan_Desingh — Fri, 14 May 2021 01:52:22 GMT

Try this,

tab1: LOAD * INLINE [ ROW, NAME, NUMBER 1, A, 500 4, A, 450 8, A, 550 10, A, 1000 15, A, 500 3, B, 400 5, B, 600 42, B, 700 ]; tab2: NoConcatenate LOAD *, If(Previous(NAME)=NAME, RangeSum(NUMBER,Peek('NUMBER_ACC')),NUMBER) As [1-SUM] Resident tab1 Order By NAME, ROW; Left Join(tab2) LOAD NAME, Sum([1-SUM]) As RESULT Resident tab2 Group By NAME; Drop Table tab1;

Re: SUM WITH PREVIOUS ROW FOR EACH NAME

Saravanan_Desingh — Fri, 14 May 2021 01:52:58 GMT

Output: