By using this website, you agree to our Terms of Use (click here)
I have an interesting problem. I am trying to build a pivot table that recaps sales by Inventory Item broken down by year and month. We have a customer that has a new 'theme' every year, and we offer similar products year over year that are catered to the new theme. I have built the GI that shows the data. I can filter and sort it by the month value (1-12), but when I open the pivot, it starts with 1 then goes to 10, 11, 12, 2, 3, etc. If I try the month name, it is alphabetically sorted. I can export the GI results to Excel and sort manually there, but I would like to be able to push the pivot link to our product development team and have it sorted in a chronological way.
One other thing I tried was adding a formula to the GI that IF the month value was 10 return 9.1(hoping to put it after month 9), but I got a conversion data type error.
My question is how can I sort the pivot table to be Jan-Dec instead of Jan, Oct, Nov, Dec, Feb, etc.
Any ideas?
Hi Daniel,
If you force the month to be 2 digits (characters, actually), then the pivot will sort them the way you want. This expression to set the Month of IncurDate as 2 digits works in a GI I tested, though there may be a simpler expression that works:
=iif(Len(Month([JPMJobCostTran.IncurDate]))=1,concat('0',CStr(Month([JPMJobCostTran.IncurDate]))),CStr(Month([JPMJobCostTran.IncurDate])))
Using that field as a column variable in a pivot table provides this result sorted the way you want:
Dianne