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:

Value Description
MillisecondPeriod used to return a relative date based on adding/subtracting milliseconds
SecondPeriod used to return a relative date based on adding/subtracting seconds
MinutePeriod used to return a relative date based on adding/subtracting minutes
HourPeriod used to return a relative date based on adding/subtracting hours
DayPeriod used to return a relative date based on adding/subtracting days
WeekPeriod used to return a relative date based on adding/subtracting weeks
MonthPeriod used to return a relative date based on adding/subtracting months
QuarterPeriod used to return a relative date based on adding/subtracting quarters
SemesterPeriod used to return a relative date based on adding/subtracting semesters
YearPeriod used to return a relative date based on adding/subtracting years

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

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Kristof Mahieu

    Hi

     

    For the intercalary dates (ex. 29th of February 2016), the function doesn’t work!

    Month and Year is wrong. 29/02/2016 stays 29/02/2016.

     

    /wp-content/uploads/2016/02/relativedate_896942.jpg

     

    Kind Regards

    Kristof

    (0) 

Leave a Reply