Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
=sum({<date={">=$(=addmonths(max(date),-6))<=$(=max(date))"}>}[RowNo()])
use dimension as month and above as expression
RangeSum(Above(sum({<date={">=$(=addmonths(max(date),-6))<=$(=max(date))"}>}[RowNo()]),0,7))
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)