topic Re: Rolling 6 Month Count in App Development

Rolling 6 Month Count

paulaoshea — Fri, 10 Dec 2021 08:28:29 GMT

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.

Re: Rolling 6 Month Count

anat — Fri, 10 Dec 2021 08:52:49 GMT

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

use dimension as month and above as expression

Re: Rolling 6 Month Count

anat — Fri, 10 Dec 2021 08:56:45 GMT

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

Re: Rolling 6 Month Count

paulaoshea — Fri, 10 Dec 2021 11:34:50 GMT

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)