Pivot table help needed

[JMH022]

I have a pivot table that organizes year-over-year sales data by month and year for current month-plus 2. I have the month and year data in columns; Month and Year are both sorted Ascending, with month listed first so the pivot will display
Nov..................Dec
2005 2006........2005 2006, etc.

The problem I am having is this...

Starting this month, my pivot table contains data for Nov, Dec, and Jan. The pivot table is placing Jan before Nov and Dec, because the table is set to sort the months in Ascending order. I need to know how to make January data to follow December while keeping the pivot table in the same display format it is currently in.

Anyone out there an expert on pivot tables that can help me out?

 

[mrhartley]

Hi,

I think I know of 2 options you can try.

1 - In your source data replace each month with a month number. So replace all Jan's with 1, replace all Nov's with 11 and so on.
So now all months will be in numerical order and not alphabetical. Not the smartest way.

2 - Replace each month with a date. Such as replace Jan's with 01/01/05, replace Nov's with 11/01/05
In the pivot table, you can now use the group funtion and group the dates by month (Jan, Feb etc) that will display the months by alpha whilst sorting them by numerically.

I hope that helps you a little.
Regards
Mark

 

[JMH022]

Mark: Thank you for your suggestions. Your item #1 may give me the same result, as the table is sorting the Month and Year separately. I will look into your #2 suggestion...it may be feasible, but I was hoping to use the fields already built instead of having to create another field.

Thanks again.

Any other suggestions anyone?

 

[Norie]

Jim

How is the date stored in the original data?

How are you getting the months in the pivot table?

 

[JMH022]

the Pivot data is extracted from an Access db I inherited. Queries within the db extract daily sales data; The db then reformats the sale date field using the 'DatePart' function and counts up the number of sales in a given month.

I think I see where you are headed with this one...use the original sale date field or concatenate the month and year fields to use as a sort field.

Am I correct?

 

[Norie]

Jim

Where I was heading was to just group by year and month on the date.

 

[JMH022]

I love this board. It really helps get you thinking. I will work on these suggestions and see what I can come up with.

If anyone has other ideas, please let me know.