Ordering fields formatted YYYY Mth in a Pivot

[Matt D Francis]

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?

 

[Roger Govier]

Hi Matt

Try using your original Date field, not the newly created text field.
Then in the PT, right click on the Date field>Group>and 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]

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