Rolling 13 month Totals, for each date in the range - Qlik Sense

[mikelutomski]

In Qlik Sense, I am trying to return an accumulative 13 month total for each month in the 13 month range.

I'm using Aggr(RangSum(Above()), but the total returned is only for the LAST month in the range.

Column_A      Column_B          Column_C

8-15                98                     1950

9-15               198                    2005

10-15              307                   1987

11-15              409                   1993

12-15              562                   2001

1-16                718                   1999

2-16                842                   1898

3-16               1052                  1947

4-16                1257                 1965

5-16                1443                 2100

6-16               1661                  2025

7-16               1894                  1997

8-16               2046                  2046

This is my current formula:

Sum(Aggr(RangeSum(Above(Count(Distinct {<[Flag_1] = {'0'}, [Flag_2] = {'1'}, Date = {'>=$(=AddMonths(Max([Date]),-12)<=$(=Max([Date]))'} >} [Account_ID]),0,13)),[Date]))

Like I said above, this works for the 2046 that is displayed for 8-16, but I need it to show all of the totals in Column_C.

Thanks.

-Mike

[sunny_talwar · Accepted solution]

May be this:

Sum(Aggr(RangeSum(Above(Count(Distinct {<[Flag_1] = {'0'}, [Flag_2] = {'1'}, Date >} [Account_ID]),0,13)), [Date])) * Avg({<Date = {'>=$(=AddMonths(Max([Date]),-12)<=$(=Max([Date]))'}>} 1)

[mikelutomski]

Thanks Sunny.

I had to make a slight adjustment, but it works.

RangeSUM(Above(Count(Distinct {<[Flag_1]={'0'}, [Flag_2]={'1'}>}[Summary Site ID])

,0,13)) * AVG( {<[Date] = {'>=$(=AddMonths(Max([Date]),-12))<=$(=Max([Date]))'}  >} 1)