Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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)