Pivot Table Group by Quarter beginning Jul

[BDrew]

I have looked everywhere for the simple solution to Grouping my Pivot Table Months by Quarter beginning July not January.

I would appreciate any assistance.

regards

BD

 

[DonkeyOte]

You could add a column at source to assign the appropriate Quarter, eg:

B1: QTR
B2: =LOOKUP(MATCH(MONTH(A2),{1,4,7,10}),{3,4,1,2})
copied down - where A2 holds date

and use QTR as your PT Field rather than grouping by Month.

AFAIK there's no way of amending the PT's quarter assumptions (ie Calendar).

 

[litrelord]

Hi,

do the months Jan to Jun belong to the following year with a 12 moth period running Jul to Jun?

If this is the case then the best way would be to use dates instead of just months, this way Excel will see the year as well and will order them properly. You can change the format of the cells in the pivot table to show only the month if you need to keep the current formatting.

Alternatively you can right click on the row or column headers int he pivot table and select move to change the order they're in.

HTH

Nick

 

[Robert E Lee]

Hi

you could create a custom list JUL,OCT,JAN,APR and then sort the row, or column, according to this list

HTH

Robert

 

[BDrew]

Thanks everyone.

I tried them all.

Finally I placed the QTR field on the database and I am up to 3mb, mostly of field labels.

The array formula produced QTR 3 for July instead of Qtr 1. Pity, it looked like an elegant solution.

regards

BD

 

[DonkeyOte]

Sorry - yes the formula posted is complete garbage!

Should read:

=LOOKUP(MONTH(A2),{1,4,7,10},{3,4,1,2})

or

=INDEX({3,4,1,2},MATCH(MONTH(A2),{1,4,7,10}))

Note: the above are not array formulae - though they do make use of inline array constants { } rather than ranges.

 

[BDrew]

Thank you DonkeyOte, your code works as elegantly as it looks.

regards

BD

 

[JSpoon]

I have looked everywhere for the simple solution to Grouping my Pivot Table Months by Quarter beginning July not January.

I would appreciate any assistance.

regards

BD

Hi, if you use the default group by quarter in the pivot table, you can overkey the quarters to read as you want e.g. Qtr 3 (July to September) can be overkeyed to 1 and so on.