Skip to Content
Author's profile photo Noel Scheaffer
Noel Scheaffer
November 25, 2015 1 minute read

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…

http://service.sap.com/sap/support/notes/2237360

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.

Noel

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Thomas Saxby
      Thomas Saxby

      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.

      RelativeDate.PNG

      Author's profile photo Noel Scheaffer
      Noel Scheaffer
      Blog Post Author

      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...

      MM'/'dd'/'yyyy hh':'mm':'ss'.'SSS a

      Here are the formulas for each variable...

      Base Date=CurrentDate()

      Base Date Minus 1 Millisecond=RelativeDate([Base Date]; -1; MillisecondPeriod)

      Base Date Minus 1 Second=RelativeDate([Base Date]; -1; SecondPeriod)

      Base Date Minus 1 Minute=RelativeDate([Base Date]; -1; MinutePeriod)

      Base Date Minus 1 Hour=RelativeDate([Base Date]; -1; HourPeriod)

      Base Date Minus 1 Day=RelativeDate([Base Date]; -1; DayPeriod)

      Base Date Minus 1 Week=RelativeDate([Base Date]; -1; WeekPeriod)

      Base Date Minus 1 Month=RelativeDate([Base Date]; -1; MonthPeriod)

      Base Date Minus 1 Quarter=RelativeDate([Base Date]; -1; QuarterPeriod)

      Base Date Minus 1 Semester=RelativeDate([Base Date]; -1; SemesterPeriod)

      Base Date Minus 1 Year=RelativeDate([Base Date]; -1; YearPeriod)

      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.

      Noel

      Author's profile photo Former Member
      Former Member

      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.