Pivot Table - Running Total Annualised

[Geoff C]

Just discovered the "Group" feature in Pivot tables, as applied to dates, and
it's brought me tantalisingly close to what I want.

I have a list that comprises 4 years worth of sales, each row being the date
a sale was made, and the amount of the sale. What I wanted was an Annualised
total for each month (i.e. all sales in the last 12 calendar months). I've
found "Running Total", but if I group the date by year and month, the running
total resets to 0 every time the year changes. If I just group by month, then
it combines all the years, producing a useless conglomeration of figures.

I've also found a trendline that can be applied to a Pivot Chart (where I'm
just recording totals per month), that averages over 12 periods. This gives
me approximately the shape of the line I want, but figures that are average
sales - not total.

Is there any way to adapt either of these methods to produce the effect I
want? I know there are plenty of other ways I could structure the data, but I
want to apply the technique to different lists of sales that will be supplied
from various sources, and want something that can be put straight on top of
other people's data without complicated reformatting.

Any suggestions gratefully received.
Geoff.

[Bernie Deitrick]

Geoff,

> If I just group by month, then
> it combines all the years, producing a useless conglomeration of figures.

You can manually set the beginning and end dates of the grouping: the only
drawback is that you will need to manually sort to put Jan (of this year)
after Dec (of last year).

HTH,
Bernie
MS Excel MVP


"Geoff C" <[email protected]> wrote in message
news:[email protected]...
> Just discovered the "Group" feature in Pivot tables, as applied to dates,
and
> it's brought me tantalisingly close to what I want.
>
> I have a list that comprises 4 years worth of sales, each row being the
date
> a sale was made, and the amount of the sale. What I wanted was an
Annualised
> total for each month (i.e. all sales in the last 12 calendar months).
I've
> found "Running Total", but if I group the date by year and month, the
running
> total resets to 0 every time the year changes. If I just group by month,
then
> it combines all the years, producing a useless conglomeration of figures.
>
> I've also found a trendline that can be applied to a Pivot Chart (where
I'm
> just recording totals per month), that averages over 12 periods. This
gives
> me approximately the shape of the line I want, but figures that are
average
> sales - not total.
>
> Is there any way to adapt either of these methods to produce the effect I
> want? I know there are plenty of other ways I could structure the data,
but I
> want to apply the technique to different lists of sales that will be
supplied
> from various sources, and want something that can be put straight on top
of
> other people's data without complicated reformatting.
>
> Any suggestions gratefully received.
> Geoff.
>
>

[Debra Dalgleish]

In the source data, add a field that calculates the first of the month
for each sale. Name the field, e.g. YearMonth, and use the following
formula in the data rows:

=DATE(YEAR(C2),MONTH(C2),1)

where the date is in column C.

Add that field to the pivot table, and use it as the base field for the
running total

Geoff C wrote:
> Just discovered the "Group" feature in Pivot tables, as applied to dates, and
> it's brought me tantalisingly close to what I want.
>
> I have a list that comprises 4 years worth of sales, each row being the date
> a sale was made, and the amount of the sale. What I wanted was an Annualised
> total for each month (i.e. all sales in the last 12 calendar months). I've
> found "Running Total", but if I group the date by year and month, the running
> total resets to 0 every time the year changes. If I just group by month, then
> it combines all the years, producing a useless conglomeration of figures.
>
> I've also found a trendline that can be applied to a Pivot Chart (where I'm
> just recording totals per month), that averages over 12 periods. This gives
> me approximately the shape of the line I want, but figures that are average
> sales - not total.
>
> Is there any way to adapt either of these methods to produce the effect I
> want? I know there are plenty of other ways I could structure the data, but I
> want to apply the technique to different lists of sales that will be supplied
> from various sources, and want something that can be put straight on top of
> other people's data without complicated reformatting.
>
> Any suggestions gratefully received.
> Geoff.
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[ebase131]

How is running total calculated by default? When I group dates by just year and month, it calculates the running total for the year. However, when I turn on QUARTERS as a third grouping for the dates, it suddenly changes and begins summing the running total by quarter instead of the year, and it is really frustrating and confusing. I want it to stay on running total for the year but also have the dates grouped by year, quarter, and month.

Is there a reason it defaults to calculating by quarter instead of by year when the quarter grouping is added?

[arlu1201]

ebase131,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.