New RelativeDate functionality for easier date manipulation

The RelativeDate function has been around a while.  It’s core function is to return a date that is relative to another date.  This was historically done by using a parameter that specified the number of days you wanted to add or subtract from a date.

For example:

=RelativeDate(CurrentDate(); -100)

Would return Today’s date minus 100 days.

Starting with BI 4.1 Support Pack 6, new functionality was added that allows you to do the same but with other date dimensions such as month, year, quarter, etc…

This was added to the product, but the documentation wasn’t updated with all, or in some case any, of the relevant details.  This has been corrected in the BI 4.2 Formulas & Calculations guide:
http://help.sap.com/businessobject/product_guides/sbo42/en/sbo42_ffc_user_guide_en.pdf

But for those of you still on BI 4.1 SP6 and above, I have documented some useful information below that can get you started:

Syntax

The proper syntax for this function is as follows:

date RelativeDate(start_date;num;period)

In some documentation, the separator between num and period was incorrectly shown as a comma.  It is indeed a semicolon so be sure to use a semicolon for this to work:

example

=RelativeDate(CurrentDate()); 1; MonthPeriod)

The above formula would return the current date minus 1 month.  (Nov 11 2015) at the time of this writing.

Valid Values for Period

The documentation in BI 4.1 also omitted the values that were valid for this parameter.  Below is a list of the ones that you can use:

Notes on using this functionality

These are pulled from the BI 4.2 documentation:

• The num parameter can be a constant, the numerical result of a function, a measure value or a numerical dimension value, and has to be an integer.
• The num parameter can be negative to return a date earlier than start_date.
• If omitted, the period parameter works with days (DayPeriod).
• When adding or subtracting months (for SemesterPeriod, QuarterPeriod and MonthPeriod), if the day does not exist in the returned month, then the last day of the returned month must be used.
• Possible values for the period parameter are: MillisecondPeriod, SecondPeriod, MinutePeriod, HourPeriod, DayPeriod, WeekPeriod, MonthPeriod, QuarterPeriod, SemesterPeriod, YearPeriod

I should also mention this post: RelativeDate function enhancement, not yet by Noel Scheaffer

It covers his testing and results (down in the comments) which shows the output that he tested for each period type.

I have asked our documentation team to make the updates to the documentation for BI 4.1.  In the meantime, I hope this info saves you some time.

Thanks

Jb