Hyperion Planning - Date Function

Hyperion Planning has the ability to input data in "Date" format, but actually the data store in Essbase is still a number with the format "YYYYMMDD", not a real date type of data. In a real case, a client wants to calculate the interval days between two given days input in Hyperion Planning. I searched some blogs to introduce using CDF to calculate the difference, but I am not good at CDF and I tried to calculate it with the following basic functions.

• @DATEDIFF
• @DATEPART
• @DATEROLL
• @FORMATDATE
• @TODATE
• @TODATEEX
• @TODAY

Please find the code as below,

The calculation logic is as below,

1. "Start Date", "End Date" are the dates input in Hyperion Planning, and they are stored in Essbase with "YYYYMMDD" format as numbers. To explain more easily, we assume

• sDate = "Start Date" = 20130101
• eDate = "End Date" = 20141120

2. Then we need to breakdown the date into its elements (Year, Month and Day), with the calculation above, we can calculate

• sYear = 2013
• sMonth = 1
• sDay = 1
• eYear = 2014
• eMonth = 11
• eDay = 20

3. Next step, we will translate the "Start Date" and "End Date" from number format to a date format with @DATAROLL function. The logic is, we set a start date 2000-01-01, and then roll the number of years (sYear - 2000), number of months (sMonth - 1) and number of days (sDay - 1) as the intervals. So that we can get the following result,

• StartDate = 2013-01-01 (Date format)
• EndDate = 2014-11-20 (Date format)

4. Last step, we just need to use @DATADIFF function to calculate the difference of two dates (StartDate and EndDate)

• nDay = 689

/*-----------------------------------------------------------------------------------------------------------------*/

Another use case is, if we want to get the number of year, month and days of the month for the current point of view. For example, if the POV is FY14, Nov, it need to return three numbers 2014, 11 and 30.

Let's create three dynamic members: nYear, nMonth and nDay. The member formulas are as below, (Assume the Planning application start year is FY05)

• nYear

2004+@COUNT(SKIPNONE,@ILSIBLINGS(@CURRMBR("Year")));

• nMonth

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;

ENDIF

• nDay

IF (@ISMBR("Jan","Mar","May","Jul","Aug","Oct","Dec"))

 31;

ELSEIF (@ISMBR("Feb"))

 IF ("nYear"/4 == @INT("nYear"/4))

  29;

 ELSE

  28;

 ENDIF

ELSEIF (@ISMBR("Apr","Jun","Sep","Nov"))

 30;

ENDIF

The result is as below,