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:
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
34 | |
16 | |
15 | |
12 | |
12 | |
10 | |
9 | |
8 | |
8 | |
8 |