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!