This Essbase discussion board is provided as a free service and dedicated to all the Essbase professionals out there!

# Re: How to pull past 12 months

October 4 2007 at 1:08 PM
No score for this post
Jake Turrell

Response to How to pull past 12 months

Here's a member formula that might help. This assumes you want to include the current month in your rolling 12 months, as opposed to a trailing 12 months. It's not the most elegant solution in that it specifies each month . . . with some thought you could probably get this working in about five lines of code, but this is off the top of my head.

IF(@ISMBR("Jan"))
@SUMRANGE("Account_to_Sum", @CURRMBRRANGE("Months", LEV, 0, , 0)) +
@PRIOR("Account_to_Sum"->"Feb", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Mar", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Apr", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"May", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Jun", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Jul", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Aug", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Sep", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Oct", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Nov", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Dec", -1, @LEVMBRS("Years",0));
ELSEIF(@ISMBR("Feb"))
@SUMRANGE("Account_to_Sum", @CURRMBRRANGE("Months", LEV, 0, , 0)) +
@PRIOR("Account_to_Sum"->"Mar", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Apr", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"May", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Jun", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Jul", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Aug", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Sep", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Oct", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Nov", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Dec", -1, @LEVMBRS("Years",0));
ELSEIF(@ISMBR("Mar"))
@SUMRANGE("Account_to_Sum", @CURRMBRRANGE("Months", LEV, 0, , 0)) +
@PRIOR("Account_to_Sum"->"Apr", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"May", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Jun", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Jul", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Aug", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Sep", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Oct", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Nov", -1, @LEVMBRS("Years",0)) +
@PRIOR("Account_to_Sum"->"Dec", -1, @LEVMBRS("Years",0));

etc, etc, etc . . .

ENDIF

When you get to December, it will just have the first line (with the @CURRMBRRANGE).

Hope this helps,
- Jake

 Scoring disabled. You must be logged in to score posts. Respond to this message
Responses

1. Re: How to pull past 12 months - GlennS on Oct 4, 2:02 PM
1. Re: How to pull past 12 months - GlennS on Oct 4, 2:10 PM
2. Outline Structure - Bond on Oct 4, 3:06 PM

2. Thanks!! - Bond on Oct 8, 1:31 PM
1. Re: Thanks!! - Bringiton on Jun 4, 3:16 PM