Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 536.4K On-Premises Infrastructure
- 138.3K Analytics Software
- 38.6K Application Development Software
- 5.8K Cloud Platform
- 109.5K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.3K Integration
- 41.6K Security Software
LTM with @MOVSUM

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...
Answers
-
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...
-
Would Stuart's response work for you ->
https://www.tapatalk.com/groups/essbase/rolling-12-months-across-years-t14024.html