FCCS - calculating rolling x number of periods

A common type of financial calculation involves a rolling x number of month calculation, like a rolling 12 month net income. In HFM, there were functions and keywords we could use. There are different ways of writing the calculation, but most likely one of these:

HS.Exp "A#R12_NetIncome = A#Net Income.W#Periodic.P#CUR + A#Net Income.W#Periodic.P#CUR-1 + A#Net Income.W#Periodic.P#CUR-2 + A#Net Income.W#Periodic.P#CUR-3 + A#Net Income.W#Periodic.P#CUR-4 + A#Net Income.W#Periodic.P#CUR-5 + A#Net Income.W#Periodic.P#CUR-6 + A#Net Income.W#Periodic.P#CUR-7 + A#Net Income.W#Periodic.P#CUR-8 + A#Net Income.W#Periodic.P#CUR-9 + A#Net Income.W#Periodic.P#CUR-10 + A#Net Income.W#Periodic.P#CUR-11"

OR (assuming a December year end):

HS.Exp "A#R12_NetIncome = A#Net Income.W#YTD + A#Net Income.W#YTD.Y#PRIOR.P#Dec - A#Net Income.W#YTD.Y#PRIOR"


If only a three month rolling amount was needed, use the first approach and just stop after P#CUR-2.

In FCCS, a member formula is a good way to write the calculation (as compared to an insertion rule), but for us HFM converts, how? Essbase has a long list of functions, many of which work just fine with FCCS. The functions we want to look at now are @PRIOR, @RELATIVE, and @CURRMBR. Here are the definitions from Oracle's help website.

@PRIOR - "Returns the nth previous cell member from mbrName, in the sequence XrangeList. All other dimensions assume the same members as the current member. @PRIOR works only within the designated range, and with level 0 members."

@RELATIVE - "Returns all members at the specified generation or level that are above or below the specified member in the database outline."

@CURRMBR - "Returns the member that is currently being calculated in the specified dimension (dimName). This function can be used as a parameter of another function, where that parameter is a single member or a list of members."

So, how do we use these? I don't claim to be the best calc script writer (far from it), but these approaches work for me. For the rolling three month calc, I use this (again, assuming December year end):

IF(@ISMBR("Jan"))

"FCCS_Net Income"->"FCCS_Periodic" + @PRIOR("FCCS_Net Income"->"FCCS_Periodic"->"Dec",1,@RELATIVE("Years",0)) + @PRIOR("FCCS_Net Income"->"FCCS_Periodic"->"Nov",1,@RELATIVE("Years",0));

ELSEIF(@ISMBR("Feb"))

"FCCS_Net Income"->"FCCS_Periodic" + @PRIOR("FCCS_Net Income"->"FCCS_Periodic", 1) + @PRIOR("FCCS_Net Income"->"FCCS_Periodic"->"Dec",1,@RELATIVE("Years",0));

ELSE

"FCCS_Net Income"->"FCCS_Periodic" + @PRIOR("FCCS_Net Income"->"FCCS_Periodic", 1) + @PRIOR("FCCS_Net Income"->"FCCS_Periodic", 2);

ENDIF


Starting with the ELSE part first, the formula gets the current periodic amount and then uses the @PRIOR function to get the first prior and then the second prior month data. This will only work for March to December, though, as the @PRIOR function doesn't wrap around the year like HFM does. So, use the IF or ELSEIF to check for Jan and Feb and for the prior year data points, use the @RELATIVE function within the @PRIOR function to pull from the prior year.


For the rolling 12 month calculation, the above technique could be extended to cover 12 months, but as with the HFM formulas above there is a more elegant way.

"FCCS_Net Income"->"FCCS_YTD" + @PRIOR("FCCS_Net Income"->"FCCS_YTD"->"Dec",1,@RELATIVE("Years",0)) - @PRIOR("FCCS_Net Income"->"FCCS_YTD"->@CURRMBR(Period),1,@RELATIVE("Years",0));


This formula pulls the current month YTD value, adds the prior year December YTD value, and then subtracts the current month, prior year YTD value. The @CURRMBR function is inserting the current month being calculated into the dimension reference for the @PRIOR function. As I type this I'm not sure if this is needed, but as seems to be working I'm leaving it alone.



In closing, Essbase has been around since the early 1990s, so there has been plenty of time to figure out the functions that are needed - us HFM converts just need to learn!

FCCS - Blocks

Those who are coming to FCCS from Essbase or Planning know all about blocks. But for those coming from HFM or Enterprise or MicroControl, blocks are a new concept.

BRIEFLY, blocks are how data is stored in FCCS. A block is a combination of any stored sparse and dense member. Each combination of stored sparse dimensions is a separate block. But all members in a dense dimension is stored in one block. In FCCS, the Account dimension is the only dense dimension, so all accounts are in the block for a given combination of sparse dimensions.

Blocks get automatically created when loading data but not with calculations. You write a perfect calc, deploy, consolidate, and get no result. It looks like the calc either didn't run or didn't work. What actually happened is the calc worked but since there was no block there was no place to store the result.

So, what to do? On the rule insertion points, there is an option to enable automatic block creation.

On the bottom right of the above screenshot, you'll see an option to auto create blocks. Clicking No will change it to Yes with the following warning.

So now when you consolidate, the calc will run as before but now there will be a place to store the data.

There are other ways to create blocks, but this is the simplest (other than loading data).

FCCS - Calculations, FIX, and Restricted Dimensions

As FCCS now has configurable calculations, many people with HFM backgrounds, like myself, are learning Essbase calc script; the language used for these calculations. I haven't written an Essbase calc script before this month since 2002, so it's been a while. I'm not going to go through all of the details in one blog post (veteran Essbase consultants describe writing calc script as an art not a science) but I do want to point out one parallel for those with HFM backgrounds.

In HFM there are five dimensions that cannot be on the left side of a HS.Exp function, which is used to calculate almost everything. These dimensions are scenario, year, period, entity, and value. So we use If/Then statements to control when rules run for these dimensions.

In FCCS, one of the main functions in Essbase calc script is FIX/ENDFIX. FIX basically limits the members of dimensions to those members on which a calculation should run. The online help (link provided below) uses an example of fixing on a product and then doing a units sold calculation for that product.

There are five dimensions that cannot be used in FIX statements, however. These are scenario, year, period, view, and entity. Note that four of these are exactly the same as HFM. When a user starts a FCCS consolidation, they are using these dimensions as parameters for the consolidation and so the software is automatically fixing on these dimensions. And when you get past the FIX statement and into the actual calculation, these same five dimensions cannot be on the left hand side of the calculation, just like HS.Exp. So, just like you would do in HFM, us a IF/ENDIF to control when calculations run for these dimensions.

Here is the link to the FCCS help to learn more.

https://docs.oracle.com/en/cloud/saas/financial-consolidation-cloud/agfcc/configurable_calc_working_with_essbase.html

Have fun!