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

Re: How to pull past 12 months

October 4 2007 at 1:08 PM
No score for this post
Jake Turrell 
from IP address 63.171.234.11


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, 2007, 2:02 PM
    1. Re: How to pull past 12 months - GlennS on Oct 4, 2007, 2:10 PM
    2. Outline Structure - Bond on Oct 4, 2007, 3:06 PM
     
  2. Thanks!! - Bond on Oct 8, 2007, 1:31 PM
    1. Re: Thanks!! - Bringiton on Jun 4, 2012, 3:16 PM
     

RSS feed for this forum - http://www.network54.com/Forum/58296?xml=rss. Please email hypess (at) gmail.com, if you have any questions/feedback/issues.