Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old January 24th 06, 09:53 AM posted to microsoft.public.excel.worksheet.functions
tysop
 
Posts: n/a
Default pivot table and groups

I'm not sure if this is the best board to post this to, but I'll give it a try.
I have a pivot table containing data for the past 9 months or so, which I
have grouped by month and year.
However I have 2 problems:
firstly I want to see a running total with Januarys figure added to
Decembers running total figure, not restarting in January.
Secondly I want to compare January to December to get a
% difference from value, but I only get a #N/A error unless I just group by
month. Which will be OK until data runs over a year.

If anyone has any suggestions tehy would be much appreciated.

Thanks


  #2   Report Post  
Old January 24th 06, 06:33 PM posted to microsoft.public.excel.worksheet.functions
Debra Dalgleish
 
Posts: n/a
Default pivot table and groups

To see a running total, you could add a field to the source data, in
which you calculate the year and month, e.g.: =TEXT("yy-mm")
Add that field to the pivot table, instead of year and month.

Use the new field as the base for the running total.

To see the difference between Jan and Dec, you could create a copy of
the pivot table, with year in the column area and month in the row area.
Set the data to show % Difference from, with Date as the base field, and
Jan as the Base item.
Hide all months except Jan and Dec.

tysop wrote:
I'm not sure if this is the best board to post this to, but I'll give it a try.
I have a pivot table containing data for the past 9 months or so, which I
have grouped by month and year.
However I have 2 problems:
firstly I want to see a running total with Januarys figure added to
Decembers running total figure, not restarting in January.
Secondly I want to compare January to December to get a
% difference from value, but I only get a #N/A error unless I just group by
month. Which will be OK until data runs over a year.

If anyone has any suggestions tehy would be much appreciated.

Thanks



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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
flattening aggregated fields in pivot table Randall Arnold Excel Discussion (Misc queries) 2 November 4th 05 08:42 PM
Sort a Column of Dates in Pivot Table Linny Excel Worksheet Functions 2 September 23rd 05 01:24 AM
Pivot Table Subtotals/Variance Analysis NCW Excel Discussion (Misc queries) 1 August 6th 05 07:23 PM
Group Field in Pivot Table – Not Enough Room Mary Ann Excel Discussion (Misc queries) 2 July 15th 05 08:22 AM
pivot table yllee70 Excel Worksheet Functions 1 February 21st 05 10:49 PM


All times are GMT +1. The time now is 05:59 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017