How to capitalize interest payments in Hyperion Planning / Essbase

You may meet a requirement like this: In calculation interest payment for a contract funding for a project, before the project finished, the interest payment is Capex Expense. After the project finished, all the interest payment of that contract will become OPEX expense. How do you model this in Hyperion Planning ? 

The problem here is:

1. How to get current Month and Year on FIX Statement and convert them into compariable form.
2. How to compare 1. and the Project Finished date: Hyperion Calc and script is not good in supporting Date and Time calculation, Project’s Finished Date is stored as number (yyyymmdd, e.g: 20131215). So how to compare 20131201 with “Dec” and “FY13″?

Below is what I have done. The syntax shows below can be done in an Essbase calc script or Hyperion planning business rule. 

VAR FM; /* Finished Month of Project*/

VAR FY; /* Finished Year of Project */

VAR CM; /* Capture Current Month on FIX statments */

VAR CY;  /* Capture Current Year on FIX statments*/

FIX ( @RELATIVE( “Year”, 0), @RELATIVE( “Period”, 0), @IDescendants( “Projects”)….)

            FY = @ROUND( “FinishedDate”->”NA Period” / 10000, 0);

            FM = @MOD( @ROUND( “FinishedDate”->”NA Period” / 100, 0), 100);

            /*  For FY13 return 2013… */

            CY = @JgetDoubleFromString( @CONCATENATE( “20”, @SUBSTRING( @NAME( @CURRMBRRANGE( Year, Lev, 0, 0, 0)), 2))); //For some unknow reason, @CURRMBRRANGE work faster than @CURRMBR

            /* Set CM value based on currrent Period On FIX statement  */

            IF ( @ISMBR( “Jan”))

                CM = 1;

            ELSEIF ( @ISMBR( “Feb”))

                CM = 2;

            …..

            ELSEIF ( @ISMBR( “Dec”))

                CM = 12;

            ENDIF

            IF ( CY < FY OR ( CY == FY AND CM < FM))

                Do something…

            ELSE

                Do something…

            ENDIF

ENDFIX

In case you are running into errors you can also refer to my other blog post that talks about optimization of Essbase calculations.