Feb 08 2021 09:03 AM
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
Feb 08 2021 11:18 AM
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)
Feb 08 2021 11:49 AM
@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?
Feb 08 2021 11:58 AM
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.
Feb 09 2021 07:18 AM
@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?
Feb 09 2021 07:27 AM
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.
Feb 11 2021 03:38 AM
@Hans Vogelaar you're my hero! Thanks again for your help. :)