topic Re: Sub-Running Sum By ID in QlikView

Sub-Running Sum By ID

mario-sarkis — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Sub-Running Sum By ID

Vegar — Wed, 08 Jul 2020 07:21:20 GMT

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

;

Re: Sub-Running Sum By ID

mario-sarkis — Wed, 08 Jul 2020 09:50:39 GMT

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

Re: Sub-Running Sum By ID

Vegar — Wed, 08 Jul 2020 11:07:53 GMT

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

Please explain the difference?

Re: Sub-Running Sum By ID

Vegar — Wed, 08 Jul 2020 11:27:55 GMT

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

Take look at the image below.

Re: Sub-Running Sum By ID

Kushal_Chawda — Wed, 08 Jul 2020 11:54:52 GMT

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;