Tech Tips: Calculation Manager Custom-Defined Date Functions

ACE.png Celvin Kattookaran, Huron Consulting Group

calendar.jpg

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

1.jpg

2.jpg

2.    In this example 10 years are added, go back a month and add 3 days

3.jpg

4.jpg


@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.

5.jpg

6.jpg

@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.

7.jpg

8.jpg

@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.

9.jpg

10.jpg

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.

Recent Stories
New Oracle Professional Scholarship Applications Are Now Open

ODTUG April 2019 News

The Nomination Deadline for the 2019 ODTUG Innovation Award Has Been Extended!