I have a Priority field that has numeric characters to one decimal place and alphanumeric characters that have a "P" in front of the priority number (for Pending projects).  Does anyone know how to make this sort properly?  The screenshot below will show the sort is incorrect.  For example, it puts 1.11 before 1.2, 1.3, etc.  Thanks in advance for any suggestions.

Hi,

The sort order appears correct because 1.11 is less than 1.2.

I don't think there's going to be a way to sort it the way you want using the values that are in that field.  Since some records have alpha characters, it's going to treat the whole field as alpha and sort accordingly.

One way to do it would be to create a synthetic column using the Split function to get the whole number value and then a second synthetic column to get the decimal value.  Then sort on the whole number first, then the decimal.  

HI Doug,

As far as I can tell the column is sorting the data correctly as it is doing a TEXT based sort (a-Z). In this type of sort all numbers are treated as TEXT (and so are decimal points). In a text based sort (a-Z) sort order is numbers (0-9) followed by punctuation (*,"\ etc.) then letters a-z. In a Text sort you will find the number 200 will be listed before the number 3. I think this (without further details) is happening because the Priority column type is set to Text - the 'A' symbol in Apptio V11.

If you are wanting a numeric sort (which I suspect you are) I would consider creating a column transform, where the new column is NUMERIC and populate it with your Priority column values removing the 'P' if one exists. Then sort on the new transformed column data.

Regards,

Chris

Maybe over-complicated, but should work:

Add three hidden columns to your report table:

- The first could be called 'Lettered' or similar, with a formula of:

      =If(Upper(Trim(Left({Primary},1)))="P","P","")

- The second could be called 'BeforeDecimal' or similar, with a formula of:

      =If(Find(".",Trim(Substitute(Upper({Primary}),"P","")))>0,

         Split(Trim(Substitute(Upper({Primary}),"P","")),1,"."),

         Trim(Substitute(Upper({Primary}),"P","")))

- The last could be called 'AfterDecimal' or similar, with a formula of:

      =If(Find(".",Trim(Substitute(Upper({Primary}),"P","")))>0,

         Split(Trim(Substitute(Upper({Primary}),"P","")),2,"."),

         "")

Sort first by 'AfterDecimal' ascending, then by 'BeforeDecimal' ascending, and last by 'Lettered' ascending.

*You may have to wrap the split statements in 'Value(<split>)' tags to get it to work correctly.

**The before and after columns should be numeric.

Not sure if you want the P's separate from the others or merged in by number only?

Ideally you move the P to be a suffix and set everything to two 'decimal' places (they are not really decimals but secondary integers) so make your 1.1 into 1.01.

You could try a simple If base Substitute, to do this in a formula, if necessary, roughly along the lines of Tommy's suggestion.

Or, you could do the switch by checking the length of the string, so If(LEN(string)=3,Substitute(string,".",".0")