Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
mikelutomski
New Contributor III

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

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

1 Solution

Accepted Solutions

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

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)

2 Replies

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

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
New Contributor III

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

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)