Notice: WP_Scripts::localize was called incorrectly. The $l10n parameter must be an array. To pass arbitrary data to scripts, use the wp_add_inline_script() function instead. Please see Debugging in WordPress for more information. (This message was added in version 5.7.0.) in /var/www/wp-includes/functions.php on line 5535

HSP Date Functions in 11.1.2.4

G’day All,

As promised – my final post on time\date factors\arbitrary rants about date systems.

This time, we delve under the covers to have a look at some of the new HSP functions – which massively simplify any work that you need to do with dates in Hyperion!

Quick Recap

H’okay. If you’ve read back through my previous posts about time in Planning and Essbase (specifically this one and that one) or alternatively if you’ve ever beaten your head against the desk repeatedly about the seemingly cumbersome way in which essbase and planning use dates then you’ll be aware of the following:

    • Planning and essbase have different date systems
    • What works in Planning doesn’t work in Essbase
    • What works in essbase doesn’t work…basically full stop
    • It’s really difficult to efficiently use dates
    • The ‘simplest’ method is often to convert dates into effective strings and do ‘math

But, with the new HSP functions – that’s pretty much all changed.

Surprise Documentation!

Since these functions have been added there have been screams (well…modest complaints) about the staggering lack of documentation. Fortunately some amazing members in the EPM community have taken it upon themselves to change this, and through a combination of blog posts over the years have added many notes:

Alp Burak Beder – HSP Custom Functions 1
Alp Burak Beder – HSP Custom Functions 2
Cameron Lackpour – Calc Manager BSO and PLanning
Celvin Kattookaran – Calc Manager Execute MAXL and MDX
Robert Gideon – Calc Manager HSP Update 11.1.2.3
Robert Gideon – 11.1.2.4 New Functions

And now it seems Oracle has come to the party – adding the following to their Calc Manager \ Business rule Documentation.
Oracle Working with Custom Defined Functions

So let’s have a look then!
Okay, so perhaps most importantly with these functions – they are designed to work directly on Planning input dates. As such, they are expecting an Essbase date in the format (“YYYYMMDD”) and a Planning Date in the selected date format in the Planning apps.
DateSettingsinPlanning

The nice thing about this (for you crazy Americans) is that you can use discrete date options per user, but still use the same underlying functions. See date entry in mm-dd-yyyy in Planning, while the Smartview retrieve is showing dd-mm-yyyy.
Crazy_American_Dates

Or. You know. Your could standardise.
Standards

The Juicy Bit
Where was I….

Below is a listing of almost every date function in 11.1.2.4 – I’ve put in the function, the usage and the outputs (in both essbase and planning).

All Date HSP Functions

Some interesting functions in that list: CalcMgrRollDate – This is a cool function! Only for Month and Day

@CalcMgrRollDate:
[Adds or subtracts (up or down) a single unit of time on the given date field without changing larger fields.
For example,
@CalcMgrRollDate(19960131,"month",@_true) will result in the date of 19960229.
@CalcMgrRollDate(19960131,"day",@_true) will result in the date of 19960101.]
This is very useful. Rolling forward the ‘last day’ in the month without needing to deal with differing month lengths. It’s also setup for leap years, so massively reduces coding.

Another useful function is @CalcMgrGetCurrentDate. At first I couldn’t get it working, but after a quick google landed me on Robert Gideon’s blog and I was on my way. Could be invaluable if you wanted to script a data export to pass a date to the filename.

I did have issues with @CalcMgrGetDatePart and @CalcMgrAddDatePart returning slightly odd values or no values hence their exclusion above. However the main component of their functions have been replaced by more discrete functions, so they have limited utility anyways.

Anyways – I’ll hopefully be back soon with another blog post on the REST of the HSP functions. Any questions \ comments, hit me up in the comments.

Cheers
Pete

3 Comments

  1. Comment: “Some interesting functions in that list: CalcMgrRollDate – This is a cool function! Only for Month and Day”
    That was just the example. It supports day, month, year and week.

    I am Planning to add the following excel functions as CDF:
    @CalcMgrExcelACCRINT
    @CalcMgrExcelACCRINTM
    @CalcMgrExcelAMORDEGRC
    @CalcMgrExcelAMORLINC
    @CalcMgrExcelCOUPDAYBS
    @CalcMgrExcelCOUPDAYS
    @CalcMgrExcelCOUPDAYSNC
    @CalcMgrExcelCOUPNCD
    @CalcMgrExcelCOUPNUM
    @CalcMgrExcelCOUPPCD
    @CalcMgrExcelCUMIPMT
    @CalcMgrExcelIPMT
    @CalcMgrExcelCUMPRINC
    @CalcMgrExcelPPMT
    @CalcMgrExcelDB
    @CalcMgrExcelDDB
    @CalcMgrExcelDISC
    @CalcMgrExcelDOLLARDE
    @CalcMgrExcelDOLLARFR
    @CalcMgrExcelMDURATION
    @CalcMgrExcelDURATION
    @CalcMgrExcelEFFECT
    @CalcMgrExcelFV
    @CalcMgrExcelFVSCHEDULE
    @CalcMgrExcelINTRATE
    @CalcMgrExcelNPV
    @CalcMgrExcelIRR
    @CalcMgrExcelISPMT
    @CalcMgrExcelMIRR
    @CalcMgrExcelNOMINAL
    @CalcMgrExcelNPER
    @CalcMgrExcelPMT
    @CalcMgrExcelPRICE
    @CalcMgrExcelPRICEDISC
    @CalcMgrExcelPRICEMAT
    @CalcMgrExcelPV
    @CalcMgrExcelRATE
    @CalcMgrExcelRECEIVED
    @CalcMgrExcelSLN
    @CalcMgrExcelSYD
    @CalcMgrExcelTBILLEQ
    @CalcMgrExcelTBILLPRICE
    @CalcMgrExcelTBILLYIELD
    @CalcMgrExcelXIRR
    @CalcMgrExcelXNPV
    @CalcMgrExcelYIELDDISC
    @CalcMgrExcelYIELDMAT
    @CalcMgrExcelYIELD
    @CalcMgrExcelCOMBIN
    @CalcMgrExcelFACT
    @CalcMgrExcelFACTDOUBLE
    @CalcMgrExcelMULTINOMIAL
    @CalcMgrExcelFLOOR
    @CalcMgrExcelCEILING
    @CalcMgrExcelGCD
    @CalcMgrExcelLCM
    @CalcMgrExcelMROUND
    @CalcMgrExcelODD
    @CalcMgrExcelEVEN
    @CalcMgrExcelPRODUCT
    @CalcMgrExcelPOWER
    @CalcMgrExcelROUNDUP
    @CalcMgrExcelROUNDDOWN
    @CalcMgrExcelSQRT
    @CalcMgrExcelSQRTPI
    @CalcMgrExcelSUMSQ
    @CalcMgrExcelSUMPRODUCT
    @CalcMgrExcelAVEDEV
    @CalcMgrExcelSTDEV
    @CalcMgrExcelVAR
    @CalcMgrExcelVARP
    @CalcMgrExcelMEDIAN
    @CalcMgrExcelDEVSQ
    @CalcMgrExcelNTHSMALLEST
    @CalcMgrExcelNTHLARGEST
    @CalcMgrExcelDATE
    @CalcMgrExcelDATEDIF
    @CalcMgrExcelWEEKDAY
    @CalcMgrExcelDAYS360
    @CalcMgrExcelEDATE
    @CalcMgrExcelEOMONTH
    @CalcMgrExcelYEARFRAC
    @CalcMgrExcelNETWORKDAYS
    @CalcMgrExcelWORKDAY
    @CalcMgrExcelWEEKNUM
    @CalcMgrExcelMONTH
    @CalcMgrExcelDAY
    @CalcMgrExcelDAYOFYEAR
    @CalcMgrExcelDAYSINMONTH
    @CalcMgrExcelHOUR
    @CalcMgrExcelMINUTE
    @CalcMgrExcelSECOND
    @CalcMgrExcelYEAR
    @CalcMgrExcelADD

    Please let me know if these would be useful to the folks. I had reached out to Robert Gideon on these.
    If you have any feedback, please ping back to me at sreekumar.menon@oracle.com

    1. Hey Sree!

      To be honest, I actually couldn’t get CalcMgrRollDate week working (I don’t think I tried year).

      That list of CDF’s sounds amazing! You may have already seen my post on getting IPMT working – so it’s fantastic that they might be available by default. I’ll definitely hit you up for a sneak preview!

      Cheers
      Pete

  2. Hi Pete

    Good list of all the functions but I have to add this one to the list.. CalMgrDatetoString as it returns SimpleDateFormat used in java meaning you can convert data to a member as an example:

    @MEMBER(@CalcMgrDatetoString(20170101, “MMM”)))))) = Jan or
    @MEMBER(@CONCATENATE(“FY”, @CalcMgrDatetoString(20170101, “YY”)))) = FY17

    Cheers
    Dave

Leave a Reply