It's all about Enterprise Performance Management

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 the that contract will become Opex expense.

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:

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