Using Calculated Item to add dynamic columns in pivot table

[Deecrypt]

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

 

[Debra Dalgleish]

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 PivotTable>Formulas>Calculated 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]

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

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 PivotTable>Formulas>Calculated 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

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?

 

[Deecrypt]

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

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

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 PivotTable>Formulas>Calculated 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

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]

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.

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 PivotTable>Formulas>Calculated 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

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]

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.

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 PivotTable>Formulas>Calculated 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?

 

[Deecrypt]

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

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.

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 PivotTable>Formulas>Calculated 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]

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.

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.

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 PivotTable>Formulas>Calculated 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?

 

[Deecrypt]

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

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.

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 PivotTable>Formulas>Calculated 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?

 

[Roger Govier]

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

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

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.

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 PivotTable>Formulas>Calculated
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?

 

[Deecrypt]

Just a follow up on previous post, The auto sort does not work even
after I have hidden the grouped date field.
Cheers
Khurram

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

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.

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 PivotTable>Formulas>Calculated 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?

 

[Deecrypt]

Thank you for pointing out my mistake Roger. I stand corrected and it
does work. Sorry for the late reply as I was off for a couple of days.
Thank you to both you and Debra.

Cheers
Deecrypt

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

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

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 PivotTable>Formulas>Calculated
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?