kansaskannan
12/17/2006 5:32:00 AM
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
>
>
> "kansaskannan@gmail.com" 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
> > >
> > >
> > > "kansaskannan@gmail.com" 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!
> > > >
> > > >
> >
> >