topic Re: Rolling Sum over last 90 days in App Development

Rolling Sum over last 90 days

aga-cich — Sat, 16 Nov 2024 17:36:40 GMT

Hi,

I want to get this column "RollingSum90" which should be the sum of [Amount] within the same Category (here: A or B) for the preceding 90 days.

I could make this in Excel (see the photo above). The formula in D2 is this:

=SUMIFS(C1:C9; B1:B9; "=" & B2:B10; A1:A9;">=" & A2:A10-90; A1:A9;"<" & A2:A10)

In SQL I would do something like OVER ( PARTITION BY ... ). But I don't know how to translate this kind of thinking into Qlik!

Thanks,

Agata

Re: Rolling Sum over last 90 days

Ksrinivasan — Fri, 15 Jan 2021 18:29:29 GMT

hi,

Try this

RangeSum(Above(Amount,0,90))

Ksrinivasan

Re: Rolling Sum over last 90 days

aga-cich — Mon, 18 Jan 2021 08:34:15 GMT

It's not enough and I don't know how to do it to filter the rows that need to be summed up.

I don't have all the dates here in the Date columns, I need to check that the date falls into the right interval. E.x. D5 is a sum of zero rows because there are no rows with Category A in the Date interval [A5-90, A5). Also, this needs to be within the same category so I also need to look at that.