DateOffset
The DateOffset() text function (valid for relational tables as well as date-type values in planning and essbase grids) will calculate a new date offsetting the referenced date by the specified offset-amount. The offset-amount is based in the units specified (either days, months, or years). The table-name is optional if the text function appears in a relational-table or if there is only a single relational-table in the report, otherwise the table-name needs to be provided. The row reference is optional, and will be the current-row if not provided. The unit parameter is required and needs to be either day, month, or year.
Syntax
DateOffset("Grid/TableName",
row-reference,ol-reference,
calendar-unit, offsetAmount)
Parameters
Grid/TableName - this is an optional parameter if the text function
is used in a text cell of the grid/relational table. If the text function is used in
a Text object, the grid/table-name parameter is optional if there is only a single
grid/relational-table in the report, otherwise the grid/table-name parameter is
required.
row-reference - this is an optional parameter. If no row-reference
parameter is given the 'current' row (the row that the text function occurs in )
will be used (this is similar to the way text functions like CellValue() and
CellText() work. If the date-offset text function occurs in a text object, the
row-reference is required. The row reference can include an offset (eg: a
row-reference of 1(5) will return the attribute-value for the 5th tuple in row 1).
The referenced column must reference a date or timestamp value in the datasource,
otherwise an empty string will be returned.
col-reference - this is an optional parameter. If no col-reference
parameter is given the 'current' col (the col that the text function occurs in )
will be used (this is similar to the way text functions like CellValue() and
CellText() work. If the date-offset text function occurs in a text object, the
col-reference is required. The col reference can include an offset (eg: C(E) will
reference the 5'th attribute-name in column C). The referenced column must reference
a date or timestamp value in the datasource, otherwise an empty string will be
returned.
calendar-unit - The calendar unit is a required parameter. It can be
either: day, month, or year; and will determine in what unit of measurement the date
difference offset is calculated.
offsetAmount - The offset-amount is required and must be a positive
or negative integer (not zero). If the offset-amount is positive, it must include
a leading '+' symbol (eg: +3).
Note:
If either row-reference or col-reference refer to a non-date value, the function returns an empty string.
There is no additional validation done when a DateOffset() function references a column/cell which does not contain a date. The function will return an empty string in this case.
As specified in the parameter description, if the offsetAmount is a positive number, it must be proceeded by a plus symbol '+'. If the the plus symbols is missing, and there is no negative symbol, a syntax error will occur. This is required to properly be able to parse the text function string.
Example
If the date value in the referenced column B is 'Jul, 21, 2023':
- DateOffset(B, day, +4) will return Jul 25, 2023
- DateOffset(B, day, -4) will return Jul 17, 2023
- DateOffset(B, month, +1) will return Aug 21, 2023
- DateOffset(B, month, -1) will return Jun 21, 2023
- DateOffset(B, year, +2) will return Aug 21, 2025
- DateOffset(B, year, -2) will return Jun 21, 2021