topic Re: Rolling 3 Month with Aggr on two dimensions in App Development

Rolling 3 Month with Aggr on two dimensions

Jahnavi1191 — Thu, 16 Sep 2021 14:05:06 GMT

Hello,

I need to show Rolling 3 Months (Offset of 1 month i.e in Sept show: Jun+Jul+ Aug). The calculated is to be made at Parent ID level and by month but displayed by Child Id, Parent ID and Month like screenshot attcached. I was able to go through a lot of posts and this formula works when I have 1 or more selections but by default it shows NULL . Kindly help me with the right formula .

Formula I am using: Aggr( RangeSum(Above(Sum([Job Count]), 1, 3)), [Year Month])

#aggr #pivot #Set

@sunny_talwar Could really use your help here.

Re: Rolling 3 Month with Aggr on two dimensions

Kushal_Chawda — Wed, 15 Sep 2021 22:55:38 GMT

@Jahnavi1191 could you please elaborate? What is the expected output you need? If you could create excel file with expected output will be helpful

Re: Rolling 3 Month with Aggr on two dimensions

Jahnavi1191 — Thu, 16 Sep 2021 14:04:16 GMT

@Kushal_Chawda : Thank you for checking this, The output excel is attached to the post. Here is the excel version, for April I want to show jobs by Jan + Feb + March calaculate at Parent ID level.

Re: Rolling 3 Month with Aggr on two dimensions

Kushal_Chawda — Fri, 17 Sep 2021 14:22:05 GMT

@Jahnavi1191 try below

=sum(aggr(rangesum(above(sum([Jobs by Month]),1,3)),[Parent ID], (YearMonth,(NUMERIC,ASCENDING))))

Re: Rolling 3 Month with Aggr on two dimensions

kishore3k — Sat, 18 Sep 2021 06:06:44 GMT

@jahnavi11

in other way, Create a as-of Calendar and link every month in master to previous three months in as-of calendar like below.

Master Calender As-of Calendar

Jan21 Apr21

Feb21 Apr21

Mar21 Apr21

Feb21 May21

Mar21 May21

Apr21 May21

If you use the As-of calendar - Year month column for the same expression, it will calculate for rolling previous three months and absolute numbers for Master Calendar numbers.

please below screenshot except you have to map previous three months instead of equivalent+previous 2 months.

Hope this helps.