Skip to Content
Author's profile photo M Mohammed

Secret Custom Format (for Date) in Webi

Hello everyone,

Update as of 6/15/2016 9:40 AM EST:

I found that there is another secret format, to show Week Number of a Year, define the custom format as (ww). You can also use this format in a variable with FormatDate function as =FormatDate([Date];”ww”), it will return the Week Number of the Year.

Did anyone notice the secret custom format (for Date) available/that works in Webi report?

Apparently, we can easily format the Dates to show Quarters as Q1/Q2/Q3/Q4, I just discovered it for myself. I don’t know how many people knew about it or since which version is this available or is not a secret anymore, I’m using BO 4.1 SP5.

Step 1:

In a sample report, add Date column twice in a table.

Step 0.PNG

Step 2:

Right click on the 2nd column -> select Format Number -> in Format Number window, select Date/Time -> and from Sample select any one (screenshot has Sep 21, 2004 selected) -> click Custom

Step 1.png

Step 3:

Update the Custom Format

 

From (Mmm d’, ‘yyyy) (or whatever else)

Step 2.png

To (qq yyyy) as shown below and click Add to close this window and OK on the Format Number window to close it.

Step 3.png

Voila, we unlocked the secret custom format. Now the reports looks like below:

Step 4.png

Update to this post as of 10/02/2015 8:53 am (EST)

I just realized that this custom format (for Quarters) has been added in Information Design Tool’s feature where we Edit Display Format of an object. Check it out, its great and very helpful.

Update to this post as of 10/14/2015 4:12 pm (EST)

This can be done only in Java Applet mode (and Webi Rich Client) as most of you already know that HTML mode doesn’t let us create Custom Formats.

Update to this post as of 12/27/2016 3:48pm (EST):
Another secret format that works, set the custom format as ww, it shows the week number, it can be used in the formula of a variable as =FormatDate([Date];”ww”)

Thanks,
Mahboob Mohammed

Assigned tags

      22 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Good Option.

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      Thanks Ram.

      Author's profile photo WILLIAM MARCY
      WILLIAM MARCY

      Great Webi trick !

      You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !

      William

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      Thanks William.

      Author's profile photo Ravi Shankar
      Ravi Shankar

      Nice trick...

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      Thanks Ravi.

      Author's profile photo Former Member
      Former Member

      Isn't this only in applet mode? You might want to specify that, since I don't see it in interactive (HTML) mode. You can get to interactive mode by changing your preferences...

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      Updated the blog post as you suggested.

      Thanks Thomas.

      Author's profile photo Former Member
      Former Member

      Nice trick

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      Thanks Chitra.

      Author's profile photo Ray Khan
      Ray Khan

      Nice feature! Very quick and handy. Thanks for sharing.

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      Thanks Ray.

      Author's profile photo Former Member
      Former Member

      Thanks for sharing it..!!

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      Thanks Arun.

      Author's profile photo Neil Mitchell-Goodson
      Neil Mitchell-Goodson

      Not sure this is new; works in 3.1 SP7; willing to bet this goes back to the introduction of the Timedim function in 3.1SP3, if not before.

      Good job raising awareness though, lots of these things remain undocumented until people like you stumble across them 😉

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      Hi NMG,

      I didn't know it existed already, in 3.1 SP7 (and/or earlier). I've to agree with what you said, "...lots of these things remain undocumented..." 🙂

      Thanks,

      Mahboob Mohammed

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      Everyone,

      Another secret format that works, set the custom format as ww, it shows the week number, it can be used in the formula of a variable as =FormatDate([Date];"ww").

      Thanks,

      Mahboob Mohammed

      Author's profile photo JinChong Tsai
      JinChong Tsai

      Hope someone at SAP would update the "Using Functions, Formulas and Calculations in SAP BusinessObjects Web Intelligence 4.2" documentation on page 39 in section 6.1.1 Custom formats.

      Regards,
      Jin-Chong

      Author's profile photo Iva Vucak
      Iva Vucak

      Thank you, this is so helpful!

      One question - do you maybe know why format set in IDT Edit Display Format is displayed differently in Webi? Format I choose is defined as such in Webi, it is just that it is displayed as full date instead of ISO week that I set up in IDT. Thanks in advance!

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      Hi Iva,

      Thanks for stopping by at the blog!! You've a valid question, but I think SAP may also have a valid reason to why they have it like that. Simple example, in Webi Formatdate() function can take care of all the different options we see in IDT. Makes sense? Check out this blog for more info on formatdate in Webi.

      https://blogs.sap.com/2013/11/21/how-to-use-formulae-to-change-date-and-time-formats-in-webintelligence/#

      In the comments, you'll see a comment by Arijit Das, showing displaying time in different formats.

      Thanks,
      Mahboob Mohammed

      Author's profile photo Former Member
      Former Member

      Hello

      The week function is not perfect, watch out for this bug in Webi 4.1 SP5:

      FormatDate(ToDate("1/01/2017";"dd/MM/yyyy"); "ww") = 52

      FormatDate(ToDate("1/01/2017";"dd/MM/yyyy"); "yyyy-ww") = 2017-52

       

      The week calculation is also inconsistent with the weeks displayed in MS Office:

      Author's profile photo Mahboob Mohammed
      Mahboob Mohammed
      Blog Post Author

      Thanks for the info Puneet!!