|
Ordering fields formatted YYYY Mth in a Pivot
Hello
A Pivot table I have is referencing column of data that contains individual dates, the cells formatted yyyy-mm-dd. I need the Pivot to group the data by Year and Month with second row grouped underneath only so it appears as follows (YMONTH and SPECIALTY are bothrow headers) YMONTH SPECIALTY 2004 September ENT General Surgery Gynaecology Oral Surgery Paediatrics Trauma & Orthopaedics Urology 2004 September Total 2004 October ENT General Medicine General Surgery Gynaecology Ophthalmology Oral Surgery Paediatrics Trauma & Orthopaedics Urology 2004 October Total 2004 November ENT General Medicine General Surgery Oral Surgery Paediatrics Trauma & Orthopaedics Urology I have done this by creating a second date column (YMONTH) in the source data using the formula =text(date_field,"YYYY Mmm") which I've used in the pivot. This works but because it is a text field the Pivot is sorting the YMONTH field incorrectly (A-Z) 2004 Apr 2004 Jul 2004 Jun 2004 May So I've then had to arrange them in the table manually. Any ideas how I could avoid doing this? |
Ordering fields formatted YYYY Mth in a Pivot
Hi Matt
Try using your original Date field, not the newly created text field. Then in the PT, right click on the Date fieldGroupand Select both Years and Months. You would then see Year Date Specialty 2004 Sep ENT General Surgery Gynaecology .. .. Sep Total Oct ENT General Surgery etc. Be aware that Excel will not allow you to Group dates if there are any blank cells in the range, or any non-date values. Regards Roger Govier Matt D Francis wrote: Hello A Pivot table I have is referencing column of data that contains individual dates, the cells formatted yyyy-mm-dd. I need the Pivot to group the data by Year and Month with second row grouped underneath only so it appears as follows (YMONTH and SPECIALTY are bothrow headers) YMONTH SPECIALTY 2004 September ENT General Surgery Gynaecology Oral Surgery Paediatrics Trauma & Orthopaedics Urology 2004 September Total 2004 October ENT General Medicine General Surgery Gynaecology Ophthalmology Oral Surgery Paediatrics Trauma & Orthopaedics Urology 2004 October Total 2004 November ENT General Medicine General Surgery Oral Surgery Paediatrics Trauma & Orthopaedics Urology I have done this by creating a second date column (YMONTH) in the source data using the formula =text(date_field,"YYYY Mmm") which I've used in the pivot. This works but because it is a text field the Pivot is sorting the YMONTH field incorrectly (A-Z) 2004 Apr 2004 Jul 2004 Jun 2004 May So I've then had to arrange them in the table manually. Any ideas how I could avoid doing this? |
Ordering fields formatted YYYY Mth in a Pivot
yup, found this about 10 mins after posting! But thanks anyway.
usefull point about the blank cells though, I didn't realise that it explains probelms I had. Matt "Roger Govier" wrote: Hi Matt Try using your original Date field, not the newly created text field. Then in the PT, right click on the Date fieldGroupand Select both Years and Months. You would then see Year Date Specialty 2004 Sep ENT General Surgery Gynaecology .. .. Sep Total Oct ENT General Surgery etc. Be aware that Excel will not allow you to Group dates if there are any blank cells in the range, or any non-date values. Regards Roger Govier Matt D Francis wrote: Hello A Pivot table I have is referencing column of data that contains individual dates, the cells formatted yyyy-mm-dd. I need the Pivot to group the data by Year and Month with second row grouped underneath only so it appears as follows (YMONTH and SPECIALTY are bothrow headers) YMONTH SPECIALTY 2004 September ENT General Surgery Gynaecology Oral Surgery Paediatrics Trauma & Orthopaedics Urology 2004 September Total 2004 October ENT General Medicine General Surgery Gynaecology Ophthalmology Oral Surgery Paediatrics Trauma & Orthopaedics Urology 2004 October Total 2004 November ENT General Medicine General Surgery Oral Surgery Paediatrics Trauma & Orthopaedics Urology I have done this by creating a second date column (YMONTH) in the source data using the formula =text(date_field,"YYYY Mmm") which I've used in the pivot. This works but because it is a text field the Pivot is sorting the YMONTH field incorrectly (A-Z) 2004 Apr 2004 Jul 2004 Jun 2004 May So I've then had to arrange them in the table manually. Any ideas how I could avoid doing this? |
| All times are GMT +1. The time now is 07:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com