Pivot Table Grouping by Quarters

  • Thread starter Thread starter Gary Brown
  • Start date Start date
G

Gary Brown

I have a pivot table, which has dates as one of the fields. I have
grouped these dates, by month and also by quaters.

Is there a way that when dates are grouped by quarters, you can select
which month is included in which quarter.

Excel defaults that January, February, March are Quater 1, April, May,
June are Quarter 2 and so on.

I want August, September, October as Quarter 1, November, December,
January as Quarter 2 and so on.

My data is coming from an Oracle database, so I can't do anything with
the source data, unless I use a decode function within the SQL query.

Anyone have any ideas?
 
I suggest you create a lookup table and then any data will return the
quarter you require. You will only need 12 or 13 dates in a column eg
jan 1 feb 1 etc and in column 2 put appropriate quarter.
 
I always use an extra column in the original data to return my adjusted quarter
numbers. But it sounds like you can't do that.
 
If the returned data set is less than 65k rows you could use 'ge
external data' to extract the rows into a worksheet, create a new dat
column and then build your pivot table from that worksheet's data. Us
the 'adjacent column formula' functionality to auto poulate the colum
value

Here the default financial year starts on 1 July.
Using this as an example, add an extra column to the data with a valu
of the real date plus 184 days. Then 1 July 2005 becomes 1 Jan 2006 i
the new column. The when you group by the date in the new column "Yea
2006 Q1" would include all transactions with an original date betwee
1 July 05 and 30 Sept 05. Depending on how the date arithmetic work
out you may need to add months rather than days.

If you can get a change done to the Oracle view you can use syntax lik
(dateAcct + 184) AS QuarterDate .. or the month equivalent .. to creat
the relevant values in a new column in the view and then hit the dat
directly.

regards.
 
Back
Top