Calculations Using Dates Stored in Planning

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

Essbase stores values having a date format as numeric values. In the example above the “Start Date” 11/01/2013 is stored in Essbase as the value 20131101. If the “End Date” is 06/30/2014, you can calculate the number of months between the dates using Essbase functions. The following shows how to calculate the number of months between the “Start Date” and “End Date” using the @ROUND function:

1. Start with (@ROUND (“End Date”,-4) - @ROUND (“Start Date”,-4))/10000*12 – This step will calculate the number of months between the “End Date” year and the “Start Date” year. The result of this step will be (20140000 – 20130000)/10000 *12 or 12.

2. Add (@ROUND (“End Date”,-2) - @ROUND (“End Date”,-4))/100 – This step will calculate the number months between the start of the “End Date” year and the “End Date”. The result of the step will be (20140600 – 20140000)/100 or 6.

3. Subtract (@ROUND (“Start Date”,-2) - @ROUND (“Start Date”,-4))/100 – This step will calculate the number months between the start of the “Start Date” year and the “Start Date”. The result of this step will be (20131100 – 20130000)/100 or 11.

Combining the three steps into a single formula results in 12 + 6 – 11 or 7 months. A similar formula can also be written using the @INT or @TRUNCATE Essbase functions.

Comments:

Post a Comment:
  • HTML Syntax: NOT allowed