Sort by Smallest to Largest Incorrect

I have a table where I pulled the numerical part from a text field and used Number() to convert. When I go to Sort and specify Smallest To Largest, it does not work as expected. Seems like a silly issue, but unsure if there is some hiccup I am overlooking with converting text to a number.

What it does β†’ 10, 11, 12, 21, 31, 4, 5, 6
What is expected β†’ 4, 5, 6, 10, 11, 12, 21, 31

From General Information to Sigma Formulas and Functions

Hey iron-brian, sorting on a Number column should definitely yield the numeric order.

To help us to be able to accurately reproduce it, could you please let us know:

  • the formula you used to pull the numerical part from a text field
  • some examples of the values in that text field
  • what cloud data warehouse your data is in as different warehouses may have different behaviors when casting types

You’re also always welcome to pop open a live chat for direct support from within the Sigma app:

Screenshot 2023-10-25 at 10.07.48 AM

1 Like

Hi @garethbrickman , I hope the below details help out.

Number(RegexpExtract([ORIGINAL STRING], "[A-Z]*[-][a-zA-z]?([0-9]*)", 1))

Data is in Snowflake. Though I can find more details if helpful.

Here is an example of the source for the numerical value:

Original String Resulting Number and Resulting Sort
LM-D1-E-01 1
LM-D10-E-01 10
LM-D11-E-01 11
LM-D12-E-01 12
LM-D2-E-01 2
LM-D3-E-01 3
LM-D4-E-01 4
LM-D5-E-01 5
LM-D6-E-01 6
LM-D7-E-01 7
LM-D8-E-01 8
LM-D9-E-01 9
LM-E1-E-01 1
LM-E10-E-01 10
LM-E11-E-01 11
LM-E12-E-01 12

Here is my working reproduction, also on Snowflake:

Details of the sort applied:

Thanks @garethbrickman! I finally realized my mistake. I was not landing the value in the grouping I was expecting to end up sorted :man_facepalming: