How to Multiple PivotTables with Different Month and Day Groupings from the same Data Source [ANSWER]

How to create Multiple PivotTables with Different Month and Day Groupings from the same Data Source

When multiple Pivot Tables are created it initiates the OnLine Analytical Processing by default. Basically this translates to a smaller file size and caching. There is a way to create Pivot Tables from the same source but not have the Analytical Processing on.

The problem is when you need to have a field grouped one way (For example in Months, and a different PivotTable grouped in Days) it is not possible.

Two separate PivotTables may be created, in Excel 2013 for example, with separate caches driven from the same raw data with use of the PivotTable and PivotChart Wizard (Alt+D, P). The second (and any subsequent) time around, before finishing “Step 2 of 3” click Next and the following message box should appear:

SO25731981 example

Be sure to select No and be warned that this may significantly increase the size of your file.

 

The other option is to create grouping columns in your raw data. For example:

=TEXT(B4,”mmm”)
This will turn a date into a short Month Name. Ex. Jan

=TEXT(B4,”mmmm”)
This will turn a date into a long Month Name. Ex. January

 

Multiple PivotTables with Different Month and Day Groupings from the same Data Source