How to sort a month column stored as a text

[Siraj.Samsudeen]

I have a column called Invoice Month which stores the month and year of the invoice as text e.g. Jun 2012, Jan 2013, etc. Now, when I create a pivot table based on this column, I want the months to sort correctly.

To do this, I created 2 calculated columns in the original Invoice table - Invoice Month Code and Invoice Year. Then, I created a new table called Month with 2 columns - Month Code and Month Number. In Month Code, I put Jan, Feb, etc and in Month Number I put 1 to 12. Then I created a relationship between the tables on Month Code. Now, I created a new column which produces the values like this 201201, 201305, etc - I want to use this column to sort the Invoice Month column.

But when I select this column as the sort by column for Invoice Month, I get an circular reference error. I do not understand why sorting has to result in a circular reference. Is there any way to get around the circular reference issue or is there any other solution to this problem?

 

[MD610]

I've had this happen before with the circular reference issue on sorting and I am not sure why it happens because the solution I have still seems like it should be a circular reference if the direct method is considered to be one.

First off, you don't really need the extra table and the relationship. I think that just complicates things.

First make a Calculated Column for your sort order:

SortMonth = RIGHT([Invoice Month], 4) & FORMAT(SWITCH(LEFT([Invoice Month], 3), "Jan",1 , "Feb",2 , "Mar" ,3 ,"Apr" ,4,"May" ,5 , "Jun", 6, "Jul", 7, "Aug", 8, "Sep", 9, "Oct", 10, "Nov", 11, "Dec",12), "00")

This reproduces your sort column without the extra table or relationship. I'm not even 100% sure if it matters, but I like to make sure this column is formatted as a Whole Number. By default, it will be Text.

Next create another Calc Column that is a duplicate of your Invoice Month:

Invoice Month For Pivot = [Invoice Month]

Now use the first Calc Column to sort the new, duplicate column. This works eventhough they are both still referencing the same original column.

Use the new, duplicate column in your pivot.

 

[Siraj.Samsudeen]

Thanks a lot MD610. <what is="" your="" name,="" btw,="" if="" you="" don't="" mind="" saying="" it<what="" btw="">

Yes, your workaround works beautifully and I can't even understand why it works.

I agree with you - I don't need to create the extra table to get a Sort month column. The first solution that came to my mind was to use a formula like the one you suggested. But I do not like writing long-switch statements in formulas. I felt that doing it in Excel is cleaner and more reusable as I can use the same table for handling the same problem if it occurs elsewhere.

Thanks again for your patience in writing such a long switch statement - Have a nice day.

BTW, what is your name, if you don't mind sharing it?</what>

 

[MD610]

To each their own. Whatever solution works best for you and is the most comfortable for you is the best solution as far as I'm concerned.

Actually month to number and vice versa is a common conversion for me so I pretty much had the SWITCH ready to go. I keep a text file of my commonly used longer formulas and patterns so I can just cut and paste with a few minor tweaks to fit most situations.

Mike

 

[Siraj.Samsudeen]

Thanks Mike. That is a good idea to keep a text file of commonly used longer formulas and patterns. I will also start doing it.

 

[gand3rson]

Have u tried creating a custom sort list and saving yourself some of all that extra headache?

1. Type your sequence as a list in excel and select it
2. Select file, options, advanced, edit custom lists
3. Select import
4.close the option box

this custom list will now be avail under more sort options