Pivot Table Custom Grouping

[Mac]

Group:

I want to group DATE data into weeks and then group the weeks into months
and the months into quarters. The gouping is NON-STANDARD.

I can group the transaction data by weeks using standard 7-day grouping.
But then I want to group the weeks into months using 4-4-5. In other words,
the first month has 4 weeks, the second month has 4 weeks, and the third
month has 5 weeks. Each quarter will be made up of 3 months of 4-4-5 weeks.

After grouping by 7-days (a week), when I highlight the first 4 weeks and
attempt to group the data into a custom group, I continue to be thrown into
the Date Grouping dialog.

Does anyone have a suggestion on how to work around this??

TIA

=Mac=

 

[Frank Kabel]

Hi
I would add this grouping information as additional field with a
formula to your source data range and use this helper column in your
pivot table

 

[L. A. McClelland]

Frank,

Thank you for your expedient response. I will try your solution this
evening

Here's is a little additional info on my source of confusion:

If I group date data by month, I can custom group the months into FISCAL
YEAR quarters, i.e., where the quarters are not calendar year quarters. I
simply highlight the appropriate months on the face of the pivot table,
right-click and choose Group and Show Detail, and then Group. I then type
in a label, such as "Q1" and repeat for the other three quarters.

In the case at hand, I can group the dates into weeks, but when I attempt to
group the weeks into months, I get the (Date) Grouping dialog box. Geez, I
thought you could ROUTINELY group existing groups.

Is my thinking wrong here? Are their certain situations where this is
possible and others where it is not possible?? What are the general rules
for grouping existing groups?

Thanks again, Frank.

Anyone who has some insight into the problem I am having, please respond.

=Mac=