Hi

Debra suggested using
=TEXT(B2,"yyyy-mm")

If you had done this, then you would have 2006-01, 2006-02 ..... 2007-01
etc.
which would sort in the correct chronological order.

If you use the format "yyyy-mmm" or "yyyy-mmmm" you will continue to
experience the problem of months being in alphabetic as opposed to
chronological order.

--
Regards

Roger Govier


"Deecrypt" wrote in message
ps.com...
Hi Debra,
using the method given and a data range of October 2006 to March 2007
autosorted in descending order, I get the columns in the pivot table
in
the following order (from left to right)

Three Month Total column (containing the formula
=Month[+1]+Month[+2]+Month[+3])
2007-March
2007-January
2007-February
2006-October
2006-November
2006-December

It seems to me that the autosort is first sorting this by year (2007
being the highest which is what I want) and then sorting it by
alphabetical letter in descending order (March, January, February)
whereas I need it in chronological descending order (March, February,
January).

Its really funny but without the year, the chronological sorting works
fine. Shall I accept this as an Excel limitation and find alternative
methods?

Cheers for the help
Deecrypt

Debra Dalgleish wrote:

In the source data, you could add a column that calculates the year
and
month of the record. For example,

=TEXT(B2,"yyyy-mm")

where B2 contains the record date.

Add that field to the column area, sorted ascending, instead of using
the grouped date field.

Deecrypt wrote:
Hi Debra,
I should have explained further so its my fault really. Your
solution
definitely works for data covering 12 months. The pivot table is
to
contain data covering over 12 months thus I need it to
differentiate
between 'January 2006' and 'January 2007'.

I've tried using fields column names in the format "November 2006",
"December 2006", "January 2007". However when I try to autosort
this,
the column names are sorted in alphabetical order rather than
chronological order. What format should the month names be in so
that
these can represent a month and year and be chronologically sorted
using the pivot table's autosort function.

Thank you for the immense help

Khurram


Debra Dalgleish wrote:


You're welcome, and thanks for letting me know that it worked.

It's best to post your questions to the newsgroup, where volunteers
answer questions 24 hours a day. The public exchange of information
also
helps other Excel users, who can search the Google newsgroup
archives
for answers.

Deecrypt wrote:

Thank you Debra,
Worked like a charm. I had heard in a previous post to contact
yourself regrading advanced Pivot Table functions and that you are
releasing a book on it soon. May I contact you in the future with
such
enquiries?

Cheers
Khurram

Debra Dalgleish wrote:



You can create a calculated item that will add the three items to
its left:

Select the Month field button
On the PivotTable toolbar, choose PivotTableFormulasCalculated
Item
Type a name for the formula
In the formula box, enter a formula that refers to the Month
field, e.g.:

=Month[-1]+Month[-2]+Month[-3]

Click OK

Deecrypt wrote:


Hi all,
I have a pivot table that has a number of "Items" as rows fields
(i.e
monitor, CPU etc) and "Month" fields for column (i.e January,
February
etc) and the data is the count of each Item in a given Month.
This
table is updated every month with a news months stats.

I need a custom column that would add the last three months
count
together and display. I have no need of the Grand Total column
but if
that can be used to achieve this, I would be happy. I managed
to use a
"Calculated Field" to create the below formula:

='December 2006' +'November 2006' +'October 2006'

This works but only on existing data. Its does not change when
a new
column is added. Can anyone show me how the above code can be
changed
to only show the sum of the last three months entries?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html