@DATEROLL

The @DATEROLL calculation function for Essbase adds/subtracts time intervals to/from a date, returning another date. This function assumes a standard Gregorian calendar.

Syntax

@DATEROLL ( date, date_part, number )

Parameters

date

A number representing the date. The number is the number of seconds elapsed since midnight, January 1, 1970. To retrieve this number, use either of the following functions: @TODAY, @TODATEEX.

Date-time attribute properties of a member can also be used to retrieve this number. For example, @AttributeVal("Intro Date"); returns the product introduction date for the current product in context.

date_part

Defined using the following rule:

date_part_ex ::= DP_YEAR | DP_QUARTER |DP_MONTH | DP_WEEK | DP_DAY | DP_DAYOFYEAR | DP_WEEKDAY

Defined time components as per the standard calendar:

DP_YEAR - Year of the input date.
DP_QUARTER - Quarter of the input date.
DP_MONTH - Month of the input date.
DP_WEEK - Week of the input date.
DP_DAY - Day of the input date.

number

Number of time intervals to add or subtract.

Notes

Based on input date_part and dateroll number, the date is moved forward or backward in time.

Example: For input date May 28, 2024 and input dateroll number 5,

DP_YEAR adds 5 years to the input date (May 28, 2029).
DP_QUARTER adds 5 quarters (15 months) to the input date (Aug 28, 2025).
DP_MONTH adds 5 months to the input date (Oct 28, 2024).

Note that if using input date near the end of the month and a negative number, results could be different than you might expect. Example for date = May 31, 2025 and number = -1, the resulting date is May 1, 2024, because April only has 30 days.
DP_WEEK adds 5 weeks to the input date (July 2, 2024).
DP_DAY adds 5 days to the input date (June 2, 2024).

Example

Assume the outline has two date type members, MyDate1 and MyDate2.


Profit=@DateDiff(MyDate1, MyDate2, DP_WEEK);
Profit=@DatePart(MyDate1, DP_YEAR);
MyDate2=@DateRoll(MyDate1, DP_MONTH, 10);