- @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

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

- 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,

## No comments:

## Post a Comment