How to best do YTD calculations in Essbase

As we all know, Essbase is unmatched when it comes to performing complex calculations. I sometimes think the problem when doing member formulas or calculation scripts is not so much whether Essbase can actually do some horrendously difficult calculation, but rather choosing one of the various ways Essbase can do the task.

One example of this is something conceptually extremely simple, namely doing YTD calculations in Essbase. Before I go any further, I have to state that the built-in dynamic time series functionality in Essbase has never really done it for me. I prefer to create specific YTD members in Essbase, as I find that makes the usage in reports and analysis much easier.

So if we focus on YTD members, the question now becomes where do we put them? As these are related to the time aspect of your cube, it seems logical to plonk them into the time dimension. This is the route some of my colleagues prefer, I however do not like this, as I prefer to minimize maintenance of formulas. Assuming you have Month as your most granular time dimension level, then if you put YTD members in your time dimension, you have to create 12 new members with a member formula for each of them. And assuming you don’t make use of a separate Year dimension, but have all years in one time dimension, then you have to create a duplicate of all your months, each with their own YTD formula.

This approach obviously does not lessen maintenance. What it does offer though are easier formulas, because you specifically write a formula for each month, so you know exactly the logic for that month. In essence you hard-code the YTD logic for each. This is something else I don’t like, anything leaning towards hard-coding in Essbase probably means you are missing a more elegant, simpler solution.

The approach I like to take with YTD is to create a new dimension, let’s call it Periodicity for arguments sake. Assume for this example that years and months are in 2 separate dimensions, as you will see this approach will work even if they are not. Also assume the input data is monthly, and Essbase needs to calculate the YTD values. Then the Periodicity dimension will have only 2 members, Monthly and YTD. Monthly will be the default input member and will be stored, while YTD will be a dynamically calculated member (it could be stored as well for query performance sake, but to prove this approach we will make it YTD for now). The Periodicity member itself will be Label Only. This means the new dimension has only one stored member, and therefore will not increase the size of the cube. In addition you probably want to make it a dense dimension, especially if Time is a dense dimension.

So the issue now becomes what should the formula for YTD be? Do we have to test for each month in an IF statement and hard-code the formula per month? Definitely not, remember hard-coding is preferably to be avoided. If possible the same formula should be used across all months, irrespective of where in the year.

Luckily Essbase has some fancy built-in formulas which will make this easy, in this case specifically @CURRMBRRANGE provides the magic key to do this easily, in conjunction with @SUMRANGE. Assuming the Time dimension is called Periods, the months are at level 0, and the year dimension is called Years, the formula is:

@SUMRANGE(“Monthly”,@CURRMBRRANGE(“Periods”,LEV,0,,0));

This will always calculate the sum of months starting with the first month in your time dimension, and ending with the current month being calculated. This works because of the startoffset and endoffset settings for @CURRMBRRANGE. The ” (null value) for startoffset means start the range at the first member in the list, and the ’0′ for endoffset means stop at the current member being calculated.

The main benefit of doing YTD in a separate dimension, apart from the easy maintenance of just one formula, is that it makes it extremely easy for an end user to switch between YTD and Monthly, or do a YTD vs Monthly comparison. The user does not need to select specific YTD month members, they only need to select YTD or Monthly from the Periodicity dimension.