Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
harikattamudi
Contributor
Contributor

Rolling sum by ID and Date

Dear Experts,

I need to figure out how to do a rolling sum in the Qlik sense.

I have the data as shown below

Current table  
iddatevalue
1Jan10
1Feb20
1March30
1April40
1May50
2Feb10
2March20
2May30
2June40

 

Need the data  as below

Required Table   
iddatevalueCummulative
1Jan1010
1Feb2030
1March3060
1April40100
1May50150
2Feb1010
2March2030
2May3060
2June40100

 

Thanks for your time

5 Replies
prabhu0505
Specialist
Specialist

create table in QS

Add ID & Date as dimension

Add Only(Value) and Alt(Above(Only(value)),0)+Only(value) as Measure

clipboard_image_0.png

prabhu0505
Specialist
Specialist

Use this as second expression instead

Alt(if(IsNull(Above(Cumulative))=-1,Above(Only(value)), Above(Column(2))),0)+Only(value)

clipboard_image_0.png

sunny_talwar

Have you tried

RangeSum(Above(Sum(value), 0, RowNo()))
harikattamudi
Contributor
Contributor
Author

This works only when my table is sorted by date,  but if i sort by Id. The value changes as RowNO() value get updated based on the sort.

sunny_talwar

Try this

Aggr(RangeSum(Above(Sum(value), 0, RowNo())), id, (date, (NUMERIC)))