Date with Essbase Member Formulas

By
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.

Month Index

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

Year Index

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

Actual Date

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);

ENDIF

ENDIF

Downstream Calculations

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.


About TopDown Team

The TopDown Team includes members of TopDown Consulting who want to let the community know about webcasts, conferences, and other events. The team also conducts interviews on various EPM industry topics.

One comment on “Date with Essbase Member Formulas”

  1. Aamir says:

    Hi,
    Actual Date is returning as 20110000 and not 20110700 for Jul FY11
    However MonthIndex and YrIndex is returning correct values.

    Regards
    Aamir

Leave a Reply

Your email address will not be published. Required fields are marked *