Sorting Pivot Sequentially

[Astro_Viking]

Good Afternoon,

I am working in Office 365 Desktop version - I have some source data which includes the following 4 fields: created on (dd:mm:yy hh:mm:ss), unique ref #, type of booking, month (as a number).

When I put this data into a pivot table I need the pivot table to sort the data by the created on field and only have one entry per month per type.

When I add the created on field, it creates multiple entries for each Unique Ref #, I cant remove the field or add it as a splicer and sort by that. I've tried adding a field and numbering lines sequentially, but I still cant remove that filter from the pivot without it reverting to A-Z on the unique number. Any help would be fantastic attached images show my issue

When the filter is removed, it default sorts by URN.

Thanks

Rob

 

[RoryA]

You'd need to convert the created on field to a specific date of the month (no time portion) such as the first/last day. Then the data will sort but you should only have one entry for a given month (though I'm not sure of the relationship between Month and the created on date).

 

[Astro_Viking]

You'd need to convert the created on field to a specific date of the month (no time portion) such as the first/last day. Then the data will sort but you should only have one entry for a given month (though I'm not sure of the relationship between Month and the created on date).

Hi Rory,

thanks for the response, sorry I don't think I made it clear above, the created on date is the date that the line is put on the system, the month is the month of invoice, separate data points and will often be different, i.e. a line might be created in Dec 2022 but invoiced in Feb 2023.

Thanks!

 

[RoryA]

The only way you can achieve this:

sort the data by the created on field and only have one entry per month per type

is to have only one 'created on' entry per month per type. If the created on dates are always in the same calendar month for a month/type combination, then you can simply create a new field that is say the first of the month of the 'created' on date. If you could have 'created on' dates in different months for a given type/month combination, then you'd need to get the earliest date and use that.