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