How to use dates in Oracle HFM

Sometimes, you might want to calculate figures based on dates in Oracle HFM. For example, to calculate ‘average sales per week’ based on the start- and end date of a product. As Oracle Hyperion Financial Management (HFM) does not support out of the box functionality for entering dates, it might seem impossible to use dates. However, quite a lot is possible with dates and probably more than you imagine.

You can:

1. Enter dates or import with Oracle FDM(EE).
2. Report on dates.
3. Calculate (financial) data based on dates, for example average sales per week.

1. Enter dates in Oracle HFM

How can you enter dates? Oracle HFM does not support entering dates directly like ’01/01/2017’ or ‘1-jan-2017’, but you can present dates in these date formats. You can enter day/month/year separately in different members, like in the screenshot below. In this example, we have entered a start date in the following manner:

As an alternative, we could also enter the date as a number:

If we can enter something in a data grid, like we did in the screenshot above, we’re also able to import through Oracle FDM(EE) from a flat file or source database.

2. Report on dates

To present the date in a more user-friendly way, we can convert the date entered and store them as cell text. The dates are then presented as seen below:

For the conversion of the dates like we did above we use (on demand) rule logic, which means we can present the dates in any date format required.

3. Calculate (financial) data based on dates in Oracle Hyperion Financial Management

In addition to entering and importing dates, we can also do all sorts of calculations with these dates. For example, we can calculate the number of weeks between start and end date and calculate the average sales per week. In the example below you see the following:

• In the top part the entered/imported dates are shown.
• In the second part, the start date and end data which are stored as cell text.
• And in the bottom part:
  • The number of weeks between the start- and end date calculated through the rules.
  • Net sales for our product ‘Soccer Balls World Cup Edition’.
  • The calculated ‘Sales per week’.

Yes you can!

As shown in this blog, you can work with dates in Oracle Hyperion Financial Management (HFM) and even use them in the rules to calculate (financial) figures based on those dates. The example discussed here is just one of the possibilities, for other calculations there are options as well.

Oracle HFM has more possibilities than you might think at first glance. Discover these with our support for Oracle and our EPM Support services.

Text: Bert Dotinga