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?

Thank you in advance
Cheers

Deecrypt

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

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

A related query,
Once I've created a Calculated Item (called Tri_Monthly), it accurately
gives me a total of 3 Month columns to the left of it. However, once a
4th month is added to the table as an additional column, it appears
away from the Tri_Monthly column.

How can I force the pivot table to add additional columns to the left
of the Tri_Monthly column on table refresh. That way my latest data
will fall within the boundaries of the afore mentioned formula.

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

Thank you kindly
Deecrypt


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

If you set the Month field to Ascending sort, instead of Manual, it
should automatically place the month items to the left of the
Tri-Monthly item.
To change the sort, right-click on the Month field button
Choose Field Settings, and click the Advanced button
Under AutoSort options, choose Ascending
Click OK, click OK.

Deecrypt wrote:
A related query,
Once I've created a Calculated Item (called Tri_Monthly), it accurately
gives me a total of 3 Month columns to the left of it. However, once a
4th month is added to the table as an additional column, it appears
away from the Tri_Monthly column.

How can I force the pivot table to add additional columns to the left
of the Tri_Monthly column on table refresh. That way my latest data
will fall within the boundaries of the afore mentioned formula.

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

Thank you kindly
Deecrypt


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

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

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

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