Technology Blogs by SAP
Learn how to extend and personalize SAP applications. Follow the SAP technology blog for insights into SAP BTP, ABAP, SAP Analytics Cloud, SAP HANA, and more.
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member59613
Contributor

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:

ValueDescription
MillisecondPeriodused to return a relative date based on adding/subtracting milliseconds
SecondPeriodused to return a relative date based on adding/subtracting seconds
MinutePeriodused to return a relative date based on adding/subtracting minutes
HourPeriodused to return a relative date based on adding/subtracting hours
DayPeriodused to return a relative date based on adding/subtracting days
WeekPeriodused to return a relative date based on adding/subtracting weeks
MonthPeriodused to return a relative date based on adding/subtracting months
QuarterPeriodused to return a relative date based on adding/subtracting quarters
SemesterPeriodused to return a relative date based on adding/subtracting semesters
YearPeriodused 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

2 Comments