rolling sum with offset

[azertyh]

hello all,

i get the rolling sum with that :

=Sum(
Aggr(
RangeSum(
Above(
Sum(
MB_KAr)
, 0, 12)
)
, Année, Date)
)

i want to offset it with 12 months.

in the joined file, the data is in sheet1 ; what i want is in sheet2.

thanks.

[JordyWegman]

Hi Azertyh,

Try this:

Sum(Aggr(RangeSum(Above(Total Sum(MB_KAr),12,RowNo())),Année, Date))

Jordy

Climber

[Gysbert_Wassenaar]

The second parameter of the Above function is for the offset. Try changing it from 0 to 12.

=Sum(
Aggr(
RangeSum(
Above(
Sum(
MB_KAr)
, 12, 12)
)
, Date)
)

[azertyh · Accepted solution]

hi you 2,

I tried the (above, 12). it shows nothing.

I find it :

=Above(
Sum(
Aggr(
RangeSum(
Above(
Sum(
MB_KAr)
, 0, 12)
)
, Année, Date)
)
,12)

we need a second above because we need to offset the AGGR function.

[Gysbert_Wassenaar]

Perhaps an AsOf table works better for you. See this document: https://community.qlik.com/t5/QlikView-Documents/Calculating-rolling-n-period-totals-averages-or-oth...

[azertyh]

hello,

thanks for the suggestion.

in fact, my data has 2 550 lines, and as i want an 12 month rolling sum, the second table would be very big no?

however, i think to use a master calendar to fulfill easily all requests : the last month, YTD, YTD of last year, etc.

[Gysbert_Wassenaar]

No, the AsOf table is quite small. It only contains the months and the AsOf months. It does not contain any facts from your data table.

[azertyh]

hello,

i want just to share it.

because it's what i told in my previous message.