Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Grouping by Quarters
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? -- Gary Brown ------------------------------------------------------------------------ Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084 View this thread: http://www.excelforum.com/showthread...hreadid=527081 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Grouping by Quarters
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. -- robert111 ------------------------------------------------------------------------ robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996 View this thread: http://www.excelforum.com/showthread...hreadid=527081 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Grouping by Quarters
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. Gary Brown wrote: 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? -- Gary Brown ------------------------------------------------------------------------ Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084 View this thread: http://www.excelforum.com/showthread...hreadid=527081 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pivot Table Grouping by Quarters
If the returned data set is less than 65k rows you could use 'get external data' to extract the rows into a worksheet, create a new date column and then build your pivot table from that worksheet's data. Use the 'adjacent column formula' functionality to auto poulate the column value Here the default financial year starts on 1 July. Using this as an example, add an extra column to the data with a value of the real date plus 184 days. Then 1 July 2005 becomes 1 Jan 2006 in the new column. The when you group by the date in the new column "Year 2006 Q1" would include all transactions with an original date between 1 July 05 and 30 Sept 05. Depending on how the date arithmetic works 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 like (dateAcct + 184) AS QuarterDate .. or the month equivalent .. to create the relevant values in a new column in the view and then hit the data directly. regards.. -- steven1001 ------------------------------------------------------------------------ steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757 View this thread: http://www.excelforum.com/showthread...hreadid=527081 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Derived Columns in Pivot Table | Excel Discussion (Misc queries) | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Grouping By Date in Pivot Table | Excel Worksheet Functions | |||
Change Data In Pivot Table | New Users to Excel | |||
grouping data in a pivot table | Excel Discussion (Misc queries) |