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

[BarbaraRufino]

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

 

[GraH]

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?

 

[GraH]

As an example I did it with months and quarters. Not sure how you manage to do the week grouping? I hope not manually

 

[BarbaraRufino]

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]

As an example I did it with months and quarters. Not sure how you manage to do the week grouping? I hope not manually
View attachment 13375

Not manually. I selected group by days, grouping on blocks of 7 days.

 

[GraH]

That's not really weeks then, that kinda fooled me a bit

 

[BarbaraRufino]

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]

What I would do too. No worries. Take Care.