Performing Calculations Using Dates Stored in Planning

Oracle Hyperion Planning allows users to enter date values in forms. For example, a start date can be entered as 11/01/2021 if the format in use is MM/DD/YYYY. Although the values are entered in date format, it is possible to calculate values based on dates entered.

Oracle Essbase stores Planning date values as numeric values. For example, Essbase the start date 11/01/2021 is stored as 20211101. If the end date in Planning is 06/30/2022 (Essbase value 20220630), you can calculate the number of months between the dates using the @ROUND Essbase function as shown in this example:

1. Calculate the number of months between the end date and the start date years

   (@ROUND ("End Date",-4) - @ROUND ("Start Date",-4))/10000*12

   The calculation (20220000 - 20210000/100000*12) yields the result 12.
2. Calculate the number months between the start of the end date year and the end date.

   (@ROUND ("End Date",-2) - @ROUND ("End Date",-4))/100

   The calculation (20220600 - 20220000/100) yields the result 6.
3. Calculate the number months between the start of the start date year and the start date.

   (@ROUND ("Start Date",-2) - @ROUND ("Start Date",-4))/100

   The calculation (20211100 - 20210000/100) yields the result 11.
4. To determine the number of months between the start date and end date, subtract the result of step 3 (11) from the sum of the results of step 1 (12) and step 2 (6)

   The number of months between the start date and end date = (12+6)-11=7

   You can write a similar formula using the @INT or @TRUNCATE Essbase functions.