Celvin Kattookaran, Huron Consulting Group
All the functions described here are available from Calc Manager 11.1.2.3.502 onwards.
All the examples given below are done on a modified Sample Basic application and not from a real-world application. However, the logic can be ported to any real-world application, and I believe these functions will be of great help in Workforce and Capex modules where you’re working with Start and End date.
All these date functions expect the date to be in Planning format, which is YYYYMMDD. So get your dates straight :)
Essbase (epoch time) DATE use @DATEPART/@DATEROLL et al.
Planning DATE use the new functions
Let’s start in the order they appear in Custom-Defined Function Manager:
@CalcMgrAddDate
Syntax
@CalcMgrAddDate(date,yearsToAdd,monthsToAdd,daysToAdd)
Parameter |
Description |
date |
Any valid single member name or member combination, or a function that returns a Planning date yyyymmdd. |
yearsToAdd |
Signed Integer, specify number of years to add. Using a negative value will go back to the number of years specified. |
monthsToAdd |
Signed Integer, specify number of months to add. Using a negative value will go back to the number of months specified. |
daysToAdd |
Signed Integer, specify number of days to add. Using a negative value will go back to the number of days specified. |
Example
1. In this example, 10 years are added to the StartMonth
2. In this example 10 years are added, go back a month and add 3 days
@CalcMgrAddDays
Syntax
@CalcMgrAddDays(date,daysToAdd)
Parameter |
Description |
date |
Any valid single member name or member combination, or a function that returns a Planning date yyyymmdd. |
daysToAdd |
Signed Integer, specify number of days to add. Using a negative value will go back to the number of days specified. |
Notes
This function works similar to @CalcMgrAddDate function; the only difference is you are just mentioning days.
Example
In this example, five days are added to today’s date.
@CalcMgrAddMonths
Syntax
@CalcMgrAddMonths(date,monthsToAdd)
Parameter |
Description |
date |
Any valid single member name or member combination, or a function that returns a Planning date yyyymmdd. |
monthsToAdd |
Signed Integer, specify number of months to add. Using a negative value will go back to the number of months specified. |
Notes
This function works similar to @CalcMgrAddDate function, the only difference is you are just mentioning months.
@CalcMgrAddWeeks
Syntax
@CalcMgrAddWeeks(date,weeksToAdd)
Parameter |
Description |
date |
Any valid single member name or member combination, or a function that returns a Planning date yyyymmdd. |
weeksToAdd |
Signed Integer, specify number of weeks to add. Using a negative value will go back to the number of weeks specified. |
Notes
This function works similar to @CalcMgrAddDate function; only difference is you are just mentioning weeks.
Example
In this example, we are going back a week.
@CalcMgrAddYears
Syntax
@CalcMgrAddYears(date,yearsToAdd)
Parameter |
Description |
date |
Any valid single member name or member combination, or a function that returns a Planning date yyyymmdd. |
yearsToAdd |
Signed Integer, specify number of years to add. Using a negative value will go back to the number of years specified. |
Notes
This function works similar to @CalcMgrAddDate function; the only difference is you are just mentioning years.
@CalcMgrDateToString
Syntax
@CalcMgrDateToString(date,format)
Parameter |
Description |
date |
Any valid single member name or member combination, or a function that returns a Planning date yyyymmdd. |
format |
SimpleDateFormat used in java, have a look at http://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html for a full list of format that can be used. |
Notes
Considering the fact that Essbase deals with numbers, most of the SimplDateFormat cannot be used. (Remember the number we have in hand is yyyymmdd.)
Example
In this example, we are exporting data from a Planning application to a Reporting application, which uses Essbase date measures. DataExport gives you numbers as yyyymmdd where CalcMgrDateToString can help in formatting Planning date to Essbase date.
Notes @CalcMgrLogText(filename,msg,printdate)
@CalcMgrLogText always appends the file. You’ll have to remove the file before running the calc.
@CalcMgrDaysBetween
Syntax
@CalcMgrDaysBetween(fromDate,toDate)
Parameter |
Description |
fromdate |
Any valid single member name or member combination, or a function that returns a Planning date yyyymmdd. |
todate |
Any valid single member name or member combination, or a function that returns a Planning date yyyymmdd. |
To continue reading this article, click here.