RelativeDate function enhancement, not yet
We recently upgraded our BusinessObjects environment from 4.1 SP02 Patch 2 to 4.1 SP06 Patch 2.
One enhancement I was looking forward to is listed on Page 56 of http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41_whats_new_en.pdf. The content at that link seems to get updated with each support pack so here is the information related to the improved RelativeDate function…
You can now have more date options with the RelativeDate() function in your calculations, including Year, Semester, Quarter, Month, Week, Day and Hour, thanks to an additional period parameter to specify the period to add.
Previously, you could only calculate in days and had to convert every units (week, month, year etc) in days before using the RelativeDate() function.
Unfortunately, it doesn’t work. The description of the function does not match the documentation.
I thought it was very odd that the “num” and “period” parameters were separated by a comma (,) rather than a semi-colon (;). I have tried all sorts of variations using the comma as shown, using a semi-colon like everywhere else and guessing what some possible period values might be all with no luck.
This sort of thing troubles me and calls into question SAP’s quality and testing standards and practices. There is an SAP note on this stating that documentation on this function is not correct…
However, in my opinion this is more than just a documentation issue. I want to point out that the RelativeDate function still works if you use the RelativeDate(start_date;num_days) syntax. I hope they are able to fix this soon.
It's working for me in Rich Client on 4.1 SP6.2 (despite the documentation which doesn't seem to be updated and the error in the comma in the description).
The available period options are listed in the Operators section of the Formula Editor.
That is a great find! I can also see all of the "Period" variations in the Operators section. My suspicion was that it worked, but I just couldn't figure out how.
I created a report to test out all of the various period possibilities...
I applied the following custom format in order to see the value all the way down to millisecond precision...
Here are the formulas for each variable...
I am not sure why there are 4 hours being subtracted for MonthPeriod, QuarterPeriod and SemesterPeriod and 5 hours subtracted for YearPeriod. That seems incorrect.
It would be helpful if SAP could add this information about the Periods being available in the Operators section within the SAP Note mentioned above.
SAP does not know how to maintain its help files. Add the translation problems (name of functions change, at least in the French version, but the doc is not updated or is still partially in English) and this gives a very bad feeling to users.