Dimension Custom Sort

Looker Q&A Looker
1

Is there any way to custom sort the Dimension in not alphabetical order besides of adding extra spaces in the label name (label: “ dimension name”)?

I tried this in lookml:

dimension should be:

B

A

C

dimension: dimension_sort {

type: number

sql: ${table}.dimension_sort

oder_by_field: sort
}

dimension: sort {

type:number

sql: case when ${table}.B = ${table}.B then 1

when ${table}.A = ${table}.A then 2

when ${table}.C = ${table}.C then 3

else null

end;;

}

but it didn’t worked, dimensions are back to alphabetical order.

2

If you bring in the sort as a hidden dimension and then sort by that column, this should work

3

Your statement will always return 1 because B = B, always. Your CASE statement should be:

sql:  CASE
        WHEN ${table}.field = ‘B’ THEN 1
        WHEN ${table].field = 'A' THEN 2
        ELSE 3
      END ;;
1 Like
4

The following method may also be good:

# B or A or C
dimension: foo {
  type: string
  sql: ${TABLE}.foo ;;
  hidden: yes
}

dimension: foo_for_sort {
  type: string
  sql: CASE ${foo}
         WHEN 'B' THEN '1 B'
         WHEN 'A' THEN '2 A'
         WHEN 'C' THEN '3 C'
       END ;;
  label: "Foo"
  html: {{ value | remove_first: "1 " | remove_first: "2 " | remove_first: "3 " }} ;;
}
1 Like
5

Hi! Thank you all for your response. I tried it all but it still didn’t worked. I used the spaces before the dimension name, works fine now, I’ll use it for the mean time. :slight_smile: