Skip to Content
0
Former Member
Jul 22, 2016 at 10:03 AM

Webi 4 sorting behavior incorrect with FormatDate

1031 Views

I am editing a Webi report (using SAP Business Objects BI4). I have a report with a table in which I display a date formatted like this :

=FormatDate([Sales Date];"dd-MMM-yyyy") 

Originally, Sales Date dimension has the format "mm/dd/yyyy".

I want to sort my table by this formatted date dimension in ascending order, so I just select my table > right click > select Sort and then I select the =FormatDate([Sales Date];"dd-MMM-yyyy") in ascending order.

My problem is that, the sorting behavior is incorrect as the result is as follows :

Sales Date 
01-AUG-2006 
01-JUL-2010 
02-FEB-2006 
03-AUG-2005 

As you can see above, it seems that the sorting is only done by the "day" value and it completely ignores the "month" and "year".

My object is correctly defined as "Date" in the universe.

and the sorting is correctly done when I don't use the formatting "dd-MMM-yyyy" and keep the original format of Sales Date.

Any suggestions please? Thanks!