topic Re: Rolling sum by ID and Date in App Development

Rolling sum by ID and Date

harikattamudi — Sat, 16 Nov 2024 20:02:55 GMT

Dear Experts,

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

I have the data as shown below

Need the data as below

Thanks for your time

prabhu0505 — Thu, 19 Sep 2019 16:47:49 GMT

create table in QS

Add ID & Date as dimension

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

prabhu0505 — Thu, 19 Sep 2019 17:02:44 GMT

Use this as second expression instead

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

sunny_talwar — Thu, 19 Sep 2019 17:07:44 GMT

Have you tried

RangeSum(Above(Sum(value), 0, RowNo()))

harikattamudi — Fri, 20 Sep 2019 19:02:36 GMT

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 — Fri, 20 Sep 2019 19:13:48 GMT

Try this

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