By TopDown Team
February 22nd, 2012
One of the common functions in any Database or Spreadsheet program is manipulating Day/Date functions to drive financial calculations or use calendars to organize and report periodic data. Unfortunately, this, like many functions, does not come natively in Essbase or any OLAP-based application and requires a bit of coding. Essbase database cannot store non-numeric values such as ‘Date’ or ‘Text’ as are often required for Workforce or CapEx applications. Examples include:
- Input of Employee Hire Date, as this is often the main driver for starting salary/benefits calculation. The issue is that in Planning we typically report in terms of Months (Periods)/Years and must convert the Hire Date to a Month and Year dimension member.
- Input of Asset Purchase Date or In Service Date in any Capital Asset application in order to calculate Depreciation Expense
- Cash Flow schedules dependant on investment database
- Interest and amortization schedules and related calculations based on dates
Date Input in Planning
One of the many properties of the Account dimension is ‘Data Type’ which designates how values are stored. The selections include Unspecified, Currency, Non-Currency, Percentage, Text, and Date. If ‘Date’ is selected for Data Type, a calendar pop-up during input allows entry of a date value, which is then converted to a number and stored in Essbase.
Calculations Using Dates
As mentioned above, date input may be useful for Employee or CapEx applications and related calculations. However, Essbase and Planning do not inherently have a calendar and in order to base calculations on dates, a conversion is necessary for Period (Month) and Year dimensions or any other Time Period dimension. This can be achieved fairly easily by adding member formulas to account dimension members. In this example, we are trying to calculate current Month and current Year as a number and then compare that value with the value stored as ‘Hiredate’. For example, we may want to start noting the headcount 30 days from the Hire Date or calculate merit increases on the anniversary of this date.
Step 1: Calculate a Month Index by assigning a number to each Period.
IF(@ISMBR(“Jan”))1; ELSEIF (@ISMBR(“Feb”))2; ELSEIF (@ISMBR(“Mar”))3; ELSEIF (@ISMBR(“Apr”))4; ELSEIF (@ISMBR(“May”))5; ELSEIF (@ISMBR(“Jun”))6; ELSEIF (@ISMBR(“Jul”))7; ELSEIF (@ISMBR(“Aug”))8; ELSEIF (@ISMBR(“Sep”))9; ELSEIF (@ISMBR(“Oct”))10; ELSEIF (@ISMBR(“Nov”))11; ELSEIF (@ISMBR(“Dec”))12; ELSE #MISSING; ENDIF
Step 2: Calculate a Year Index; substitution variables can also be used here.
IF(@ISMBR(“FY11”)) 0; ELSEIF (@ISMBR(“FY12”)) 1; ELSEIF (@ISMBR(“FY13”)) 2; ELSEIF (@ISMBR(“FY14”)) 3; ELSEIF (@ISMBR(“FY15”)) 4; ELSEIF (@ISMBR(“FY16”)) 5; ELSEIF (@ISMBR(“FY17”)) 6; ELSEIF (@ISMBR(“FY18”)) 7; ELSEIF (@ISMBR(“FY19”)) 8; ELSE #MISSING; ENDIF
Step 3: Calculate the current index for each combination of Period and Year to be expressed in terms of a Date but in a number format. In this example, the fiscal year begins in July, so July FY12 is calculated as Actual Date.
“Actual Date” = 20100000 + 10000 + 700 = 20110700
IF (“YrIndex” <> #MISSING)
IF (@ISMBR(“Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”))
“ActualDate” = 20100000 + (10000 * “YrIndex”) + (“MonthIndex” * 100);
ELSEIF (@ISMBR(“Jan”, “Feb”, “Mar”, “Apr”, “May”, “Jun”)) “ActualDate” = 20110000 + (10000 * “YrIndex”) + (“MonthIndex” * 100);
Now that we have a reference point for the current Month and Year in the POV, conditional testing can be added in business rules to compare this date with the date being input. For example, Depreciation calculation may begin in the Period that the asset is being acquired without having to convert a date to the appropriate Month and Year. Or employee benefits can kick in on a particular Period/Year that is based on a ‘Hire Date’. Note that member formulas are set up as ‘Dynamic calc’ and order of precedence is important in placing these members in the outline.