Pivot Chart not sorting by date

[JonGreen]

Hi,

I cannot get the horizontal axis in the chart picture to display in date order.

I have tried around 10-15 differnt fixes suggested on similar threads, if someone could let me know where I'm going wrong that would be amazing! I've attached the file if that helps.

Cheers

Jon

[#2]

@JonGreen 

The problem is that the field (column) you used in the Rows area uses the TEXT function, so it is a text value that is sorted in alphabetic order.

Use the Date field instead, and group it by Years and Months.

See the attached version

(I also cleared the check box to reverse the order of the x-axis)

[#3]

@Hans Vogelaar brilliant! Thank you so much for helping.

In the version you sent back though, all data from Sept-Nov is missing? Any ideas?

[#4]

@JonGreen 

The Source Data of the pivot table was set to 'Edited data'!$A$1:$R$5797. It should have been 'Edited data'!$A$1:$R$6421. If you change this, the missing months will appear.

[#5]

@Hans Vogelaar thank you, but now the first change you made has reset and I can't duplicate what you did.

How have you created the 'Years' field to add to Rows and display date order rather than alphabetical? 

[#6]

@JonGreen 

I used the Date column instead of the Month/Year column in the Rows area of the pivot table.

I then right-clicked any of the dates in the pivot table and selected Group... from the context menu.

I selected only Months and Years in the dialog:

Confirm by clicking OK. this adds the Years field in the Rows area.

Since Date is a real date column instead of a text column, it is automatically sorted correctly.

See the attached version.

 

[#7]

@Hans Vogelaar you're my hero! Thanks again for your help. :)