0

I think I have tried everything to make my PivotTable sort my dates as dates and not as labels. All my date filters works on the column I am trying to sort, so I guess Excel know them as dates. However, when I try to order the columns from a-z I expected Excel to order them from low to high, but instead Excel sorts them as text, like this:

February 1, 2006
February 1, 2007
February 1, 2008
February 10, 2006
February 10, 2007
February 10, 2008
February 11, 2006
February 11, 2007
February 11, 2008
February 12, 2006
February 12, 2007
February 12, 2008

Does anyone know a solution to this and a simple solution that non-technical persons can understand and use?

pnuts
  • 58,317
  • 11
  • 87
  • 139
user3635789
  • 11
  • 1
  • 3

1 Answers1

0

Can you change the cube? Are you the developer or user? If developer can you change dates to YYYY-MM-DD? That's easiest. If not see below.

There was a similar thread that may help you if you have a member property already which is sortable: Sort by key in Excel connected to SSAS OLAP cube

Another important consideration is that if you just want standard sorting you can do that when the developer designs the dimension. Just sort by key and it should be sort correctly. And in Excel when you sort choose Data Source Order.

Community
  • 1
  • 1
GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • Formatting the dates as YYYY-MM-DD will make them sort correct but Excel will not recognize them as dates still. I will take a look at you link. – user3635789 Oct 08 '15 at 16:12
  • Also edited my post to mention about making sure the sort order is setup right when you design the dimension. – GregGalloway Oct 08 '15 at 23:10