Avatar of Murray Brown
Murray Brown
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Excel Pivot Automatically includes Quarter

Hi

With Excel Pivot tables that contain dates I noticed that a Quarter option was automatically added to the pivot check boxes on some pivots but not on others. In the attached spreadsheet this works on the sheet PREM AUD TOT but not on others. Why would this be?


Pivit 1.xls

Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Murray Brown

8/22/2022 - Mon
Fabrice Lambert

I can only guess that Excel manage a list of "interresting fields" internally and offer them for display.
Robert Berke

Column G in 4 worksheets have circular references.
for instance PREM USD'!$G$6   has formula =ROUND(E6*D6*(1-G6),2)
Try fixing those and see if things work better.

Robert Berke

You are seeing the results of using Pivot Table Grouping.  Hold on about 15 minutes while I make a good example for you.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Robert Berke

To see how Pivot table Grouping can implement calendar quarters, do the following.
  1. Open the Pivit-1 v2 NO QUARTERS.xls.
  2.  Alt N V T {enter} to start a new pivot table.
  3. Add Date, To, Total GBP
  4. Select cell with 1/10/2020
  5. Click on  pivot table tools "Options"  tab
  6. In the ribbon  take "Group Selection"
  7. Select Months Quarters and Years then click OK.

 
  1. The pivot table fields list will now have virtual fields like "Quarters". You can then deselect fields you do not want until it looks like this.

 
The new virtual fields will remain associated with the Data Source and new Pivot tables will reflect automatically show them.
But, if you delete all associated pivot tables, the virtual fields are gone and you have to rebuild them.
 
 
 
ASKER CERTIFIED SOLUTION
Robert Berke

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Robert Berke

One last point. You might have several pivot tables associated with the same data source.
When you use Group Selection to modify the Grouping
of one pivot table from Months,Quarters, Year
to become Days, Quarters, Years

Then ALL the associated pivot tables will change. 

It is possible to prevent this by assigning each pivot table to its own pivot cache, but that is a subject for a future question.

Another way is to name your data source "MySource" then create an Alias called "MySource2" which points to the same data, and base each Pivot table on a unique alias.
Murray Brown

ASKER
thanks very much Robert
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.