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.
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:
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:
The proper syntax for this function is as follows:
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:
=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:
|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.
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.
thanks Jon ..saved me a ton of time