Calculating the difference between dates within Hyperion Planning

This post will take us through a typical example of calculating the difference in days between two dates in Hyperion Planning.

Hyperion Planning has had the ability to store data as ‘Date’ for some time. But even though you set the storage type as ‘Date’, once refreshed into Essbase the member changes and ends up with the storage type as ‘number’. The date is converted into a number like 20130810, which relates to the date 10^th August 2013.

The problem with this is that the standard date functions that exist in Essbase require the date to be passed as the system date in seconds since 1st January 1970. This has always been a real pain and in the past there have been numerous ways to which you could combat the problem.

The normal approach has always been to breakdown the date into its elements (Year, Month and Day) and then using formulas work out some long winded solution to calculating the difference between dates, or carrying out any sort of date tests or calculation. Another approach could be to develop a CDF (Custom defined function), or use some of the CDF’s that are available for download from Oracle that cover date logic. See here – http://www.oracle.com/technetwork/indexes/samplecode/essbase-sample-522117.html

The problem is that unless you are comfortable with creating CDFs or are comfortable with knowing how to use the Essbase Sample code CDFs and registering them in Essbase, you are really stuck with developing long lines of complex code for some calculations using dates. Also, any sort of upgrade etc and you will need to remember to register the CDFs again.

One of the calculations that can often be difficult is calculating the difference between two user input dates in Hyperion planning in terms of days.

Well finally we are getting nearer to having the ability to do this using a standard function! (well close to a standard function :))From the 11.1.2.2 release of Hyperion Planning & Essbase, Oracle have added some more pre-installed CDF’s and specifically relating to dates, with one in particular a new CDF called @HspDateDiff. Even though this is a CDF, as it is pre-installed with Essbase so as developers you won’t need to know CDFs to an advanced level, all you need to do is know it exists and use it.

If you right click and select edit against the essbase server in EAS you will see the functions available to you, with @HspDateDiff

 

As normal there is little documented about even the pre-installed CDFs  so below I will demo the use of this new pre-installed CDF using a real life business requirement.

Let us imagine we are a business that distributes DVDs or other entertainment media. The way we forecast Revenue is to take the lifetime revenue forecast for each territory around the world for each DVD release and apply a revenue recognition profile that is based on days from the release date in each of those territories. Each day from release will relate to a cumulative percentage that revenue should be recognised at, up to 100% recognised.

So that the business can forecast the monthly forecast values, each fiscal period is assigned a period end date. This period end date for each month of the year can then be compared to the release date of the DVD release to come up with a days from release by month.

Once we have the days from release by month, we can then lookup from this to the days in the revenue recognition profile for each territory. This will give us the cumulative percentage of revenue by month and then can be applied to the lifetime revenue forecast to drive our monthly forecast.

The challenging part to this calculation is calculating the days from release. Now you can combat this by breaking down the date as I have previously referred to, but the code can become a little complex once you start spanning multiple years. Also if you require the ability to calculate ‘minus’ days from release i.e for marketing spend for example where you may incur marketing spend before the DVD is released, this can make the code even more complex.

So let us use the new @HspDateDiff CDF to combat the requirement.

Let us assume we have the below profile of period start and end dates. Periods are in the columns with lifetime value (A custom period) added to the far left. In the rows we have the accounts period start, period end dates and also the release date and cumulative days members. The period start and end dates are entered against a no product and the release date for this example will be entered against the product “A NEW DVD”

 

What we want to do is enter a release date in the Lifetime value column on the left next to “A NEW DVD” product and then see the cumulative days come back against each months columns under that release date

To test this function let us just make the cumulative days example be dynamic calc and then enter a member formula as shown below using the @HspDateDiff function. The @HspdateDiff function works by passing two dates into the function that are held in int type and in the format YYYYMMDD. So the syntax for the function is @HspDateDiff(Int,Int)

Now the difference calculated is in absolute terms and therefore if the period we are calculating is before that of the release date we will need to apply a minus to the start of the function so that we can show minus days from release.

The below formula calculates the difference in days between the release date and the period end date for each month. The first section tests to say if the release date is less than or equal to the period end, then use @HspDateDiff(Release date, Period end date) else take - @HspDateDiff(Release date, Period end date)

 So we have added the member formula and carried out a database refresh

Let us go back to the form and enter a release date of 15^th May 2013 and see the results

 

 

 As you can see, its worked. The Cumulative days example member now has negative days up to Apr and then positive days past that. With May saying a days from release of 16 (31 days in May – release on and inc 15^th May). Also Apr brining back -15 days from release as the DVD releases on the 15^th May. Also notice we have crossed financial years into FY14 and the calculation still works.

So there you go, a new simple way to calculate the difference in days between two dates in Hyperion Planning without lots of scripting or building or registering and custom CDFs!