Pivot Table sorting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LadyReader - 20 Jan 2006 20:06 GMT

I have some data being exported daily from SQL into Excel. A pivot
table is applied to the data with the columns of the table being the
WeekEnding dates. Although the field format is "date" in both the raw
data and the pivot table column, the pivot table is sorting the columns
according to alphabetic rules; e.g 01/20/2007 sorts before 01/21/2006.
And I do have the autosort option set to ascending for WeekEnding.

Can someone suggest how to get these columns to sort ascending?

Thanks.

Reply to this Message

Jim Thomlinson - 20 Jan 2006 20:33 GMT

It sounds to me like you do not actually have dates in your source data but
rather you have text. To confirm this select the datss on the raw data and
try to format them to some other date format. If they won't format then you
have text. Assuming that to be the case you can try doing a find and replace
on the column replacing 0 with 0. Oddly enough when you do that Excel
performs an implicit conversion and changes the text to dates (usually). When
that is completed go to your pivot table and refresh it twice. You have to do
it twice for the change to take effect (just a weird quirk with pivot
tables). Let me know how it goes.

Signature

HTH...

Jim Thomlinson

> I have some data being exported daily from SQL into Excel. A pivot
> table is applied to the data with the columns of the table being the
[quoted text clipped - 6 lines]
>
> Thanks.

Reply to this Message

LadyReader - 20 Jan 2006 21:07 GMT

Jim, thanks for responding. While waiting for a reply to my question, I
went searching on the web for an answer and came up with the same issue
you noted. My date column wasn't really a date, although it sure looked
like it. I rewrote the SQL export job and manually set to destination
table field to date, and it worked.
Thanks again!

Reply to this Message