LTM with @MOVSUM

elwayisgod

Hi,

So to calculate trailing twelve months, it seems the @MOVSUM formula is the answer in business rule or calc script.  When I run the below, it's not summing FY17->Nov to FY18->Oct like I desire.  The number it calc's is a weird number I can't match manually.

The first year in my outline is FY16.  So I start with FY16->Dec as my starting point as that should be the first month with 12 previous months.  My calc results in 328,307,129 somehow.  Not sure what I'm doing wrong here...

&FP_Act_Yr = FY18

&FP_Act_Per = Oct

Sales:

Nov17 - 14,302,275

Dec17 - 12,875,387

Jan18 - 13,991,387

Feb18 - 13,610,723

Mar18 - 16,138,467

Apr18 - 16,721,633

May18 - 16,641,443

Jun18 - 15,610,751

Jul18 - 17,456,337

Aug18 - 17,679,792

Sep18 - 15,754,154

Oct18 - 18,447,228

Total - 189,229,577

Fix("BaseData" , "Sales" , "Actual" , "Final" , @Relative("Year",0) , @Relative("Stats",0) , @Relative("Total Business Unit",0) , @Relative("Total Mounting Type",0) ,

    @Relative("Total Portfolio",0) , @Relative("Total Product Line",0))

"LTM" = @MOVSUM("Sales",12,@XRANGE ("FY16"->"Dec" , &FP_Act_Yr->&FP_Act_Per));

Endfix

Thanks for any insight...

elwayisgod

OK.  So this returns the correct result:

"LTM" = @MOVSUM("Actual", 12, @XRANGE(FY17->Nov , FY17->Dec), @XRANGE(FY18->Jan, FY18->Oct));

Problem is without adding more SubVars to manage not sure this can be dynamic.

Is there a better way via Dynamic Member formula or am I kinda stuck with this?  I was thinking some type of

IF @IMBR("Jan")

Then "Jan" + PriorYr "Feb" to "Dec"

However that doesn't seem like best way to do it in member formula...

Robert Angel

Would Stuart's response work for you ->

https://www.tapatalk.com/groups/essbase/rolling-12-months-across-years-t14024.html