Grouping data in pivot table - only in one pivot and not all of pivot of the sheet

BarbaraRufino

New Member
Joined
May 8, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Dear all,
Apologies if this is a basic question. I have several pivot tables based on the same data list. In some of the pivots I have dates in the "row area". I wanted to group by month in one pivot and by week in other pivot. The thing is when i choose to group by month, all the pivots in the the file update for month grouping. When I choose week grouping, all pivots assume weekly grouping. How can I apply different date group criteria in different pivots?
Thank you in advance.
Best regards
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi, that's a classic one. Grouping happens on pivot cache* level, not the pivot itself.
You can force a new cache by invoking the shortcut ALT + D + P and select the option to create a new cache. The exact wording escapes me right know, but it should be obvious what to click.
Then do the grouping.
Downside, since this multiplies the caches, so you can't share slicers and the workbook will bloat in size. I therefore prefer to add columns in my raw data that contains all time dimensions that I need (or I shift to powerPivot and use a calendar table).

*Cache is like the optimized copy of the raw data in memory.

EDIT: can't you make the grouping on both levels (months and weeks (? I don't see that option though)) then select in the row label one or the other grouping?
 
Last edited:

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
As an example I did it with months and quarters. Not sure how you manage to do the week grouping? I hope not manually :oops:
1588953178726.png
 

BarbaraRufino

New Member
Joined
May 8, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hi, that's a classic one. Grouping happens on pivot cache* level, not the pivot itself.
You can force a new cache by invoking the shortcut ALT + D + P and select the option to create a new cache. The exact wording escapes me right know, but it should be obvious what to click.
Then do the grouping.
Downside, since this multiplies the caches, so you can't share slicers and the workbook will bloat in size. I therefore prefer to add columns in my raw data that contains all time dimensions that I need (or I shift to powerPivot and use a calendar table).

*Cache is like the optimized copy of the raw data in memory.

EDIT: can't you make the grouping on both levels (months and weeks (? I don't see that option though)) then select in the row label one or the other grouping?
Thank you very much!
 

BarbaraRufino

New Member
Joined
May 8, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

As an example I did it with months and quarters. Not sure how you manage to do the week grouping? I hope not manually :oops:
View attachment 13375
Not manually. I selected group by days, grouping on blocks of 7 days.
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
That's not really weeks then, that kinda fooled me a bit :)
 

BarbaraRufino

New Member
Joined
May 8, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
That's not really weeks then, that kinda fooled me a bit :)
Instead of changing the Cache options I just added a week column on the raw data and my problem is solved! Thank you very much for your kind assistance.
 

GraH

Well-known Member
Joined
Mar 22, 2020
Messages
702
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
What I would do too. No worries. Take Care.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,319
Messages
5,595,465
Members
413,992
Latest member
CSEGUIN1973

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top