Pivot table sorting by 'unnatural' orders 1

[RRinTetons]

I have data for dates from Oct 1 through April 30 that need to be sorted in that order for a series of years to provide comparisons between the same date in 2004-2005 and 2005-2006 and 2006-2007, etc. So far, the best way I've been able to do this is to extract the month and day from the date in the data to create a text value like '10/01' or '12/24' or '01-18' and use that as the row header instead of the actual date. That, of course, fails to sort into the required order.

Excel's custom sort lists are limited in the total number of characters they can contain and don't even come close to being big enough.

I replaced the month/day strings with straight numeric values from 001 through 213 and got the ordering required but users were pretty puzzled by just what date of the season '126' might be.

Finally, I appended the '001', '002', '003', ... strings to the month/day strings to get '001-10/01', '002-10/02', etc. That works to get the ordering correct and savvy users can get the date information, but it really doesn't make it for formal presentation of the data at an executive or Board level.

For the time being, the data analysts preparing the final reports presentation have to convert the pivot table into values only and use a formula to parse off the '###-' leader on each date and prepare final output without making any changes to the data. Getting a different data set requires going back to the pivot table and going through the whole formatting exercise again.

Can anyone see a way to do this better? Is there some secret way to order the values in the row headers by a value that is neither displayed in the labels nor calculated as a value? I can get what they want in a report done using BIDS and Reporting Services, but that loses the flexibility of pivot tables.

-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

Hi,

Why not REAL DATES? Dates sort just fine, provided that they are REAL DATES and not just STRINGS that appear to be dates.

I SORT and GROUP by dates all the time.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RRinTetons]

Because I don't see a way to present the data for comparison by the month/day across multiple seasons. The output needs to look something like:

2005-2006 2006-2007 2007-2008
10/01 xxxxx xxxx xxxxx
10/02 xxxxx xxxx xxxxx
10/03 xxxxx xxxx xxxxx
10/04 xxxxx xxxx xxxxx
..... xxxxx xxxx xxxxx
04/30 xxxxx xxxx xxxxx

If I use the actual dates it comes out like:

2005-2006 2006-2007 2007-2008
10/01/2005 xxxxx
10/02/2005 xxxxx
10/03/2005 xxxxx
10/04/2005 xxxxx
..... xxxxx
04/30/2006 xxxxx
10/01/2006 xxxx
10/02/2006 xxxx
10/03/2006 xxxx
10/04/2006 xxxx
..... xxxx
04/30/2007 xxxx
10/01/2007 xxxx
10/02/2007 xxxx
10/03/2007 xxxx
10/04/2007 xxxx
..... xxxx
04/30/2008 xxxx

etc.

The goal is to compare the same month/date part for multiple seasons.

-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

In your ROW date field, right-click and select Grou & Outline > Group

In the Group Window, select DAY & MONTH.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RRinTetons]

Way closer!! Probably close enough. the only other thing I'd love would be to find a way to get the months to sort as Oct, Nov, Dec, Jan, Feb, Mar, Apr. I can move them around manually and it works just fine, though.

Thanks, I could have farted around with all kinds of byzantine machinations for hours and not been anywhere close to the right solution.




-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

Right-Click the MONTHS ROW field and select Field Settings[/v]

ADVANCED button

Sort Options Ascending using MONTHS

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RRinTetons]

Got it, with the addition of a custom list to get Oct, Nov and Dec in front of Jan, Feb, Mar.

Excel 2007 doesn't seem to have the Field Settings -> Advanced path, but selected Sort Opitons -> More Options from the drop down for the Months field in the field list got me there.

I bow in you general direction...

As long as I've got the Sun and the Moon, how about the Stars, too? Is there a way to add hierarchies of my own in there? I also need to get data out for comparison by weeks quite often. I manufactured a SeasonWeek in the dimension table for the data warehouse this stuff comes out of, but it's not as easy as I'd like it to be to add it into a pivot table. This Group By xxxx thing is cool and I'd love to find a way to put my own hierarchies in there.

-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

The MONTH sort is DATE order.

What do you mean by hierarchies?

You can GROUP by Week (by 7 DAYS).

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RRinTetons]

The Group By is a 'natural hierarchy' for

Year
Quarter
Month
Day

An alternate hierarchy would be

Year
Week
Day

Or, if I could get it

Season
Week
Day

or

Season
Month
Day

where a Season is the months Oct - April or May - Sept.

It's a different hierarchy since days don't fit into Weeks at the same boundaries they fit into Months, or because Months don't fit into Seasons at the same boundaries they fit into Years, etc.

In SSAS I can set up dimensions for dates and then define custom hierarchies on them. The hierarchies are only useful once the data has been processed into a cube, however, and at this point I don't have everything they want me to report on neatly arranged in SSAS cubes. It would be useful to be able to get the same concept of user-defined hierachies in Excel pivot tables, but it's probably also true that there's a reason SSAS exists and trying to get the same out put from Excel is pushing it a bit.

-
Richard Ray
Jackson Hole Mountain Resort

 

[SkipVought]

In your source data, add a column(s) for season & week, using a formula or a table (which I would use).

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}