How to order Looker Filter Options when string

Question

Currently, I have a filter that is a string, so it's ordering the options like this:

The order of the options is incorrect, as you can see $1M is right after $15k and before $200k.

Without prefixing the values with something "A." , "B.", "C.", etc... how do I change the order of the filter options?

Answer 1

I think you can first create a dimension like the below:

dimension: sort_arr_bands {
    type:number
    hidden:  yes
    sql: CASE
          WHEN ${arr_bands} = '<=$1M' THEN 1
          WHEN ${arr_bands} = '<=$2M' THEN 2
          WHEN ${arr_bands} = '<=$3M' THEN 3
          ELSE 4
        END;;
    description: "This dimension is used to force sort the arr_bands dimension."
  }

Then add to your main/original dimension:

dimension: arr_bands {
  type: string
  sql: ${TABLE}.arr_bands ;;
  order_by_field: sort_arr_bands
}