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

IPMT and PPMT in Essbase

G’day All,

The subtitle for this post could be “How to make sure the guy after you doesn’t hate you – making code legible”

As the newest Ace Associate and java-junky-Jason-Jones points out – code is read significantly more often than it is written

He then also used the term “behooves” correctly – so perhaps he just felt in the mood to wax lyrical.

Anyways.

The underlying logic for the functions PMT\IPMT\PPMT is not that difficult. Basically: for a fixed term loan you are to work out the full repayment, the interest component and the principle component. Obviously as you go through the loan period the ratio of interest to principle decreases, you pay more interest at the front when your principle amount is high and more principle at the end when the amount owing has diminished.

For a thorough and absolutely fantastic walk through of how to convert the excel function into code (including examples of Java & SQL!) you should definitely go and read this article by Kevin Cross . Actually – you should go and read it anyway – it’s an incredibly clear yet detailed discovery document, stepping through all of the issues, giving exceptionally clear examples (and counter-examples) and then finishes with a multitude of questions and answers for specifics. If the rest of world’s software documentation was half as good as this then there would be significantly fewer stressed developers (and a hell of a lot less random posting to stack overflow)!

And in Essbase?
So. To convert what is there to essbase functions. It should be noted that I’m cheating a little – this is really the IPMT and PMT functions in their default state, with a simple loan type and no value remaining at the end.

/* 
Tn is the length of the loan.
n is the period in question.
pv is the present value
rt is the Interest Rate
Lease Payments PMT = (rt / (1 - @POWER((1 + rt),-tn))) * pv;
Interest On Lease IPMT = (pv * rt * (@POWER((rt + 1),(tn + 1)) - @POWER((rt + 1),n))) / ((rt + 1) * (@POWER((rt + 1),tn) - 1));
Principal on Lease (PPMT) = PMT - IMPT */

No worries right? Too easy.

Where it all becomes a bit…messy

I got to actually writing this piece of the code however, and I realised I was going to have a bit of a problem. Tn (length of the loan) was a calculated field based upon a lookup off a smartlist with an override. N was a dynamic calc member. pv was a piece of data entry against a completely different intersection and rt was another lookup, with another potential of an override! Given that the IPMT code was already of a significant length, how could I make this code actually readable?

To give you an example of what this code looked like with all the cross-dimensionals (and note, this doesn’t include the overrides!)

Interest_On_Lease_IPMT = ("Total_Cost"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump * "Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 * (@POWER(("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 + 1),("Asset_Life_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump * 12 + 1)) - @POWER(("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 + 1),"Loan_Period"))) / (("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 + 1) * (@POWER(("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12 + 1),"Asset_Life_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump * 12) - 1));

Yeah. Not wonderful. And to be honest, once I add in the 3 other alternatives with the overrides, quite ridiculous.

The calculation for IPMT isn’t ever going to change – however, it’s possible (although unlikely) that the data locations might. Therefore, if down the track one of the data locations needs to be updated, somebody will need to go through this function piece by piece ensuring that they only change the right bit.

Clean the yard
So, how to make it easier? How about we make all the data locations variables instead?

var tn = 0;
var n = 0;
var pv = 0;
var rt = 0;

/* Open Calc Block */
Lease_Payments_PMT(
	/* If there is a lease override use it, otherwise use the interest rate */
	IF("Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump <> #missing)
		rt = "Interest_Rate_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12;
	ELSE
		rt = "Interest_Rate"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump/12;
	ENDIF
	/* If there is a asset life override use it, otherwise use the default asset life */
	IF("Asset_Life_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump <> #missing)
		tn = "Asset_Life_Override"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump * 12;
	ELSE
		tn = "Asset_Life_Global"-> "Unallocated Organisation"-> "Unallocated Activity"-> "Unallocated Line"-> "BegBalance"-> &Year_Assump * 12;
	ENDIF
	/* Set Present Value and Period */
	pv = "Total_Cost"->"Unallocated_Asset_Category"-> "BegBalance"-> &Year_Assump;
	n = "Loan_Period";
			
	Lease_Payments_PMT = (rt / (1 - @POWER((1 + rt),-tn))) * pv;
	Interest_On_Lease_IPMT = (pv * rt * (@POWER((rt + 1),(tn + 1)) - @POWER((rt + 1),n))) / ((rt + 1) * (@POWER((rt + 1),tn) - 1));
        Principal_on_Lease_PPMT = Lease_Payments_PMT - Interest_On_Lease_IPMT;	
);

Much easier. The code matches up exactly with the actual function (thus making it significantly easier should somebody ever need to additional interest functions) and it’s also much easier to review (and change!) the data locations should they ever need to be updated.

And trust me. The guy after you will thank you for it.

Cheers
Pete