Sub-Running Sum By ID

[mario-sarkis]

Hello Qlikers,

I want to Calculate the Sum Field By ID as per the below example, How that you can help with this. Thank you!

ID	Count	Sum
1	1	6
1	0	5
1	1	5
1	0	4
1	1	4
1	0	3
1	1	3
1	1	2
1	1	1
2	1	4
2	0	3
2	1	3
2	1	2
2	1	1

[Vegar · Accepted solution]

You will need to do this in the script. 

You can do something like this

Load

  [ID] ,

  [Count] ,

  if(peek('ID') =[ID] , Peek('Sum') +[Count], [Count]) as [Sum]

From Source

Order by [ID] //makes sure you process one ID at the time

;

[mario-sarkis]

Hello Thank you for the reply,

I am trying the below expression in the script but it is not giving the required output of calculated the sum. The output sum is calculating the followed Rows as the screenshot below: Hope you can help with this. Thanks again

ID	Count	Sum
1	1	5
1	0	4
1	1	4
1	0	3
1	1	3
1	1	2
1	1	1
2	1	4
2	0	3
2	1	3
2	1	2
2	1	1

[Vegar]

It looks quite similar to me. You have less rows in your second example than in your first.

Please explain the difference? 

[Vegar]

I've created a qvw where I calculate the same output as your expected output.

Take look at the image below.

[Kushal_Chawda]

You may need to create additional step to sort the data

Data:
LOAD 
    ID,
    "Count"
FROM [lib://Qlik]
(html, utf8, embedded labels, table is @1);

New:
NoConcatenate 
Load *,
     if(Peek(ID)<>ID,1,rangesum(Peek(Rank),1)) as Rank
Resident Data
Order by ID; // here you cn sort the data way you want to present.

Drop Table Data;

Final:
NoConcatenate
Load *,
    if(Peek(ID)<>ID,Count,rangesum(Peek(Sum),Count)) as Sum
 Resident New
Order by ID,Rank desc;

Drop Table New;