Planning Date Type to Unix Time

Hyperion Planning ships with a feature that allows you to create Date type measures for those times when a calendar date is relevant. (Hire Date for employees, Purchase Date for assets, etc...) This is a cool way to represent calendar days on planning forms and to have the value presented to a user as an actual calendar date. See below for an example of this date type measure in action on version 11.1.2.3.

Date data type in Hyperion Planning

So far this is pretty straight forward. A user picks a date on a calendar control like the one shown above and the value is stored in Essbase where other mathematical goodness takes place. If you have some use case for the Essbase date-related functions and you are using Hyperion Planning to store Date type measures I think you will find this post interesting.

Quick Detour

Pretend for a moment that we could not keep track of time until a recent day in history. Perhaps midnight on January 1, 1970 is the date (henceforth known as “the Epoch”). From this day forward time would continue to accumulate from 0 seconds to now. Here is a quick description of the Epoch from a Linux manual (man) page for the date command:

the date and time represented by seconds, where seconds is the number of seconds since the Epoch (00:00:00 UTC, January 1, 1970; see time(3)), and can be specified in decimal, octal, or hex.

Many systems can work with this date format (including Essbase). Basically 0 (zero seconds) represents January 1, 1970 and every day after that is an additional 86,400 seconds. When all time is relative to the Epoch, then 86400 seconds = January 2, 1970, and so on.

So far so good? Great! I am going to try to wrap up this post by 1406394000.

Back to Planning and Essbase

So now that we’ve had a quick detour, how does this relate to Hyperion Planning and the date type measure we showed above? In planning, dates are eventually stored as a number in Essbase in the following format: YYYYMMDD. For example, today’s date would be stored as 20140726.

Planning Date Data Type in Essbase.

This is a number that can be used within calculations in many simple cases but unfortunately Essbase date-related functions are worthless without converting this number format to something else first. (i.e. number of seconds since the Epoch.) There are other Essbase functions that can convert a String to a Date using a format specification. However, number to String conversion in Essbase calculation scripts would require something like a custom defined function (CDF) to perform the conversion. With a date formatted as the number of seconds since the Epoch you can then use it with one of the following Essbase functions:

With these functions you can do interesting things with dates and answer certain questions. (How many days, weeks months, etc... between two dates? What week of the year is this date? What would the ending date be 5 weeks from now? and so on.) It would be convenient if Planning could write date values back to Essbase in this format natively but as you have seen above, this is not the case. Usage of a CDF is a viable option, however, this is not necessarily always possible or desirable. I would vote for a native Essbase function that could handle this conversion or to have Planning write to this format natively since you can extract parts of the day with the @DATEPART function if needed.

Putting It All Together

There is currently no built-in Essbase function to work with the “YYYYMMDD” date format in planning applications. At least, not that I have seen but we are also not stuck with using CDF functions to convert our dates. We can use our knowledge of the Unix time format and the planning date format to convert Planning Date to Unix Date with a little bit of Essbase math as follows:

Note: “Planning Date” is assumed to be some Date data type measure from your planning application.

@DATEROLL(0, DP_MONTH, (
    ((@INT("Planning Date"/10000) - 1970) * 12) 
    + @INT(@MOD("Planning Date",10000)/100) - 1)
)
+ (86400 * (@MOD("Planning Date",100) - 1));

Let me break this into smaller pieces for you to walk through what is going on here.

@DATEROLL - A function that you can use to “roll” a Unix date by some amount
0 - The Unix Epoch time of 1/1/1970
DP_MONTH - increment the date by some number of months
(@INT(“Planning Date”/10000) - 1970) * 12) - get the number of months from the Epoch to “1/1 of Planning Date” by extracting the number of years past and then multiplying that number by 12 months.
+ @INT(@MOD(“Planning Date”,10000)/100) -1) - get the number of months represented by “Planning Date” and add to the former month count to arrive at a total number of months since the Epoch. (subtract 1 because January is double counted in our total.)
+ (86400 * (@MOD(“Planning Date”,100) -1)) - get the number of seconds based on the Day of the month in “Planning Day”. (subtract 1 because the 1st day of the month is double counted in our total.)

... so essentially, roll January 1, 1970 forward by some number of months and daysIf we stop at Step 5 in the list above this calculation will produce 1404172800 which is July 1, 2014. To finish this off I need to add the days (minus 1) from the date stored in Essbase, to the total number of seconds Step 6 will produce 1406332800 which is July 26, 2014 in Unix time.

Unix Date command to display Epoch seconds as UTC date.

Final Thoughts

So now you can convert Planning date values into the number of seconds since January 1, 1970 and based on your understanding of the Epoch, dealing with the number of seconds since the Epoch could be solved several different ways. (Rolling forward the months + Days, starting from a fixed point in time, etc…). I have essentially opted for rolling months forward in my example including the days. If you want to save some typing in your calculation scripts you can also create an additional member in Essbase such as “Planning Date Epoch” with a version of the formula noted above. Then you can do something like this in the rest of your calculation scripts:

/*
    How many weeks, months, days, etc... between two dates
*/
"Weeks Remaining" = @DATEDIFF(@TODAY(), "Planning Date Epoch", DP_WEEK);

There are interesting notes in the Essbase technical reference about the way that Essbase treats the calendar with the various date functions and I recommend that you take a look to understand the expectations. Also, if you believe the warning in the documentation then the date conversion using these native Essbase functions will not work beyond December 31, 2037 so keep that in mind if you need to do some long range date calculations. You may end up needing to work around this limitation.

Cheers!