Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Pivot Table - Running Total Annualised
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. |
#2
|
|||
|
|||
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" wrote in message ... 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. |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sub Total In Pivot Table | Excel Discussion (Misc queries) | |||
Using a Pivot Table Calculated Field to get a Unique Count | Excel Worksheet Functions | |||
pivot table | Excel Discussion (Misc queries) | |||
Pivot Table Problems | Excel Discussion (Misc queries) | |||
Pivot table (Running Averages) | Excel Worksheet Functions |