By TopDown Team
December 28th, 2014
For those who may have missed our earlier posts, we are ringing in the New Year with a countdown of our top 10 most viewed blog posts during 2014. To see the features that ranked in the 10th through 5th spots, visit our blog home page here.
Our 4th highest viewed blog post, “Time Travel with Essbase,” was first published in 2012. The feature teaches readers how to translate spreadsheet-like concepts to Essbase functions, using the example of calculating rolling 12 months with range functions. The original post has been slightly updated. To learn more, see the current version below.
Wrapping up the 4th blog post on our list takes us into the final three features in our countdown. Check back in tomorrow to find out which topic takes the #3 spot.
Time Travel with Essbase [updated]
I was reading the biography of Albert Einstein—the most fascinating pieces are on how he developed some of his greatest theories by observing his natural surroundings. In one experiment, performed in his head rather than in a lab, he challenged Isaac Newton’s concept of space and time, in what eventually would become the Special theory of Relativity (STR). Einstein would picture in his mind such things as what would be like to ride alongside a light beam or a path the bullet takes being fired from a moving train. Anyone who is new to Essbase, might feel like he or she is reading the theory of relativity looking at the code or trying to de-mystify the Block Storage or Dense/Sparse settings, but Essbase calculations are thankfully not nearly as mysterious as the laws of nature. Many people have figured out how to translate spreadsheet like concepts to the multi-dimensionality of Essbase.
One of these is expressing Time and Periods in ways other than Months, Quarters or Years. Deriving fiscal YTD or MTD numbers out of Essbase is not always straightforward. Examples include adding a Rolling 12 or 18 months to your reports or simply to take a trend or moving average of actual data and plug it to the forecast—this is often more complicated when Period and Years are separate dimensions. Another is calculating social security taxes in a tax year when the fiscal years do not coincide with calendar years. Here is one example of using range functions to calculate Rolling 12 months:
The member Rolling12 dynamically sums last 12 months using range function @SUMRANGE which could be written as
@SUMRANGE(“Input”, <StartMonth>:<EndMonth>) but I would have to add a bunch of IF/ENDIF statements for each month and Year combination as I roll through the year. So I take the @SUMRANGE function and replace one of the variables with another very useful function, namely @CURRMBRRANGE which provides a dynamic list of months. This function can pick any number of members going forward and also backward from the level of members of the outline. I then make a SUMRANGE for current year (going back 11 months, note the negative 11) as well as one (going forward to Dec note positive 11) for the prior year. This is nesting 2 functions, much like nesting 2 or more Excel formulas:
@SUMRANGE(“Input”->FY15”, @CURRMBRRANGE(“Period”, LEV, 0,-11,0)) +
@SUMRANGE(“Input”->”FY14″, @CURRMBRRANGE(“Period”, LEV, 0,+1,11));
I could of course replace @SUMRANGE with any other Financial or Statistical range function such as @AVGRANGE or @MOVAVG. The beauty of the function @CURRMBRRANGE is that it provides a moving range relative to the current member being calculated, much like the bullet Einstein imagined firing from a moving train, OK, maybe that is not the perfect analogy! Again note that function @CURRMBRRANGE on itself cannot calculate anything, but rather must be nested inside a function that does, such as @SUMRANGE. The Essbase Admin Guide provides a full listing of all functions, and lists all the related variables for each.
Another very useful range function is @XRANGE, which returns a list of members from 2 specified single or cross-dimensional set of members, say period and years. I found this useful in situations where one might want to calculate a moving average over a specified time period, such as last three months:
Again @XRANGE is a member list so it must be nested within a calculator like @MOVAVG. There are a few considerations with regard to cross-dimensional members, the dimensions must be ordered the same and follow one another in the outline. The order of the variables matters, so for example, one cannot write
Now, there are a lot more fun Essbase functions that reconstruct mathematical or statistical models such as Linear Regression to fit past data into a line or a curve in order to project future. But trust me it is not as complex as the theory of relativity!