Rolling 6 Month Count

[paulaoshea]

Hi All,

I've looked at previous posts on how to calculate rolling n month aggregations but still can't seem to get this to work for my scenario. I want to add a column to the below that calculates the sum complaints for the previous 6 months. So in Jan 2021 I would want total complaints for the previous 6 months (even though they are not displayed in the table)

My rolling 6 month sum is 

=Count({<CREATED_MONTH={">=$(=MonthStart(AddMonths(Max(CREATED_MONTH),-6)))<=$(=MonthEnd(AddMonths(Max(CREATED_MONTH),-1)))"}>}COMPLAINT_ID)

But this obviously is wrong! Any help greatly appreciated,

Thanks.

[anat]

=sum({<date={">=$(=addmonths(max(date),-6))<=$(=max(date))"}>}[RowNo()])

use dimension as month and above as expression

[anat]

RangeSum(Above(sum({<date={">=$(=addmonths(max(date),-6))<=$(=max(date))"}>}[RowNo()]),0,7))

[paulaoshea]

Hi Anat,

thanks for the prompt reply. None of those solutions produce the desired result for me:

Rolling6_1:

=COUNT({<CREATED_DATE={">=$(=addmonths(max(CREATED_DATE),-6))<=$(=max(CREATED_DATE))"}>} COMPLAINT_ID)

Rolling6_2:

=RangeSum(Above(sum({<CREATED_DATE={">=$(=addmonths(max(CREATED_DATE),-6))<=$(=max(CREATED_DATE))"}>}RowNo()),0,7))

Could it perhaps be that in my table Period is defined in the load script as MonthName(CREATED_DATE)