Pivot Table: Totals for "Last 12 months" and "Preceding 12 months"
 

I have a Pivot Table which shows revenues for each month in a rolling
24-month period. (That is, the 24 months are not two calendar years,
but start any month and end 24 months later). I need to add:
subtotals for the last 12 months, and the 12-months prior to the last
12 months.

Apr -2001 $2,471,802
May-2001 $2,637,046
...
...
...
Feb-2002 $2,323,360
Mar-2002 $1,328,335

Last 12Month ????????
Prec 12Month ????????

How do I do this with VBA in the Pivot Table itself? Or will I have to
do this in Excel, after the Pivot has been calculated?

Thank you!

Pivot Table: Totals for "Last 12 months" and "Preceding 12 m
 

Thank you, Jim, for your response and advice. Do I understand you
correctly to mean:

Step 1. In Source Data create two columns for Current 12 month and
Prior 12 month totals.
Step 2. Then any month (=row) that I pick will have a corresponding 12
month and 24 month total.

??


Jim Thomlinson wrote:
You need to add a flag to your Source Data that will indicate Current 12 or
Prior 12 based on the year and month. That will allow you to divide your
periods for aggregation. Then add that to your pivot table... Let me know if
that does not make sense.
--
HTH...

Jim Thomlinson


" wrote:

I have a Pivot Table which shows revenues for each month in a rolling
24-month period. (That is, the 24 months are not two calendar years,
but start any month and end 24 months later). I need to add:
subtotals for the last 12 months, and the 12-months prior to the last
12 months.

Apr -2001 $2,471,802
May-2001 $2,637,046
...
...
...
Feb-2002 $2,323,360
Mar-2002 $1,328,335

Last 12Month ????????
Prec 12Month ????????

How do I do this with VBA in the Pivot Table itself? Or will I have to
do this in Excel, after the Pivot has been calculated?

Thank you!

Pivot Table: Totals for "Last 12 months" and "Preceding 12 m
 

Not really... Just add a single extra column. Each cell will contain either
the words "Current" or "Prior" based on the date in that row. So it will look
something like this...

Date Amount flag
1-Nov-05 100 Prior
1-Dec-05 200 Prior
1-Jan-06 300 Prior
1-Feb-06 400 Current
1-Mar-06 500 Current

Now you have a field that you can add to your pivot and aggregate by...

flag Date Total
Current 1-Feb-06 400
1-Mar-06 500
Current Total 900
Prior 1-Nov-05 100
1-Dec-05 200
1-Jan-06 300
Prior Total 600
Grand Total 1500

--
HTH...

Jim Thomlinson


" wrote:

Thank you, Jim, for your response and advice. Do I understand you
correctly to mean:

Step 1. In Source Data create two columns for Current 12 month and
Prior 12 month totals.
Step 2. Then any month (=row) that I pick will have a corresponding 12
month and 24 month total.

??


Jim Thomlinson wrote:
You need to add a flag to your Source Data that will indicate Current 12 or
Prior 12 based on the year and month. That will allow you to divide your
periods for aggregation. Then add that to your pivot table... Let me know if
that does not make sense.
--
HTH...

Jim Thomlinson


" wrote:

I have a Pivot Table which shows revenues for each month in a rolling
24-month period. (That is, the 24 months are not two calendar years,
but start any month and end 24 months later). I need to add:
subtotals for the last 12 months, and the 12-months prior to the last
12 months.

Apr -2001 $2,471,802
May-2001 $2,637,046
...
...
...
Feb-2002 $2,323,360
Mar-2002 $1,328,335

Last 12Month ????????
Prec 12Month ????????

How do I do this with VBA in the Pivot Table itself? Or will I have to
do this in Excel, after the Pivot has been calculated?

Thank you!

Pivot Table: Totals for "Last 12 months" and "Preceding 12 m
 

Jim,
Appreciate your follow-up, and your suggestion turned out to be just
what was needed.
A big 'thank you'.
kannan

Jim Thomlinson wrote:
Not really... Just add a single extra column. Each cell will contain either
the words "Current" or "Prior" based on the date in that row. So it will look
something like this...

Date Amount flag
1-Nov-05 100 Prior
1-Dec-05 200 Prior
1-Jan-06 300 Prior
1-Feb-06 400 Current
1-Mar-06 500 Current

Now you have a field that you can add to your pivot and aggregate by...

flag Date Total
Current 1-Feb-06 400
1-Mar-06 500
Current Total 900
Prior 1-Nov-05 100
1-Dec-05 200
1-Jan-06 300
Prior Total 600
Grand Total 1500

--
HTH...

Jim Thomlinson


" wrote:

Thank you, Jim, for your response and advice. Do I understand you
correctly to mean:

Step 1. In Source Data create two columns for Current 12 month and
Prior 12 month totals.
Step 2. Then any month (=row) that I pick will have a corresponding 12
month and 24 month total.

??


Jim Thomlinson wrote:
You need to add a flag to your Source Data that will indicate Current 12 or
Prior 12 based on the year and month. That will allow you to divide your
periods for aggregation. Then add that to your pivot table... Let me know if
that does not make sense.
--
HTH...

Jim Thomlinson


" wrote:

I have a Pivot Table which shows revenues for each month in a rolling
24-month period. (That is, the 24 months are not two calendar years,
but start any month and end 24 months later). I need to add:
subtotals for the last 12 months, and the 12-months prior to the last
12 months.

Apr -2001 $2,471,802
May-2001 $2,637,046
...
...
...
Feb-2002 $2,323,360
Mar-2002 $1,328,335

Last 12Month ????????
Prec 12Month ????????

How do I do this with VBA in the Pivot Table itself? Or will I have to
do this in Excel, after the Pivot has been calculated?

Thank you!