Custom Sort on custom dimension

Looker Q&A Looker
1

Hi,

I have a model and there is one custom dimension in it. Now I want a apply custom sort on custom dimension (neither ASC nor DESC)

Hypothetically.
Let;s say these are values given by custom dimension.
Men
Women
Kids
Regular
Others

I want a specific sort like.
Regular
Women
Men
Others

Any idea how to achieve this.

2

I think the only way would be to create a derivative dimension with numerical value, add it to your dataset, hide from visualisation. Unfortunately it’s manual…

if(${gender} = 'Regular', 0,
    if(${gender} = 'Women', 1,
        if(${gender} = 'Men', 2, 3)
    )
)
3 Likes
3

Yup, I have to do it manual.

If Microsoft excel has feature of custom sort to achieve desired sort order then why not looker.

any way, thanks Dawid for reply.

4

For a slightly more robust solution:

@SaurabhD , if you can avoid using a custom dimension and instead put it in LookML (should be possible, I don’t think there’s anything possible in custom dimensions that you can’t do in LookML), you can use this parameter to sort in a custom direction.

It’s basically the same thing, just a little cleaner since you don’t have to expose a new dimension in the results.

5

@izzymiller ,

So you mean something like this will work…

Order_List (Regular,Women,Men,Others)

dimension: xyz_name {
sql: ${TABLE}.xyz_name ;;
order_by_field: ${Order_List}

1 Like
6

You’d still have to create some kind of 1:1 mapping between the xyz_name values and the order_list values, like in @Dawid ’s example. In LookML, you’d probably use a case when, something like

dimension: order_list {
type:number
sql: CASE WHEN ${xyz_name} = 'Regular' THEN 1
          WHEN ${xyz_name} = 'Women' THEN 2
          etc.
END
7

Thanks @izzymiller ,

Sure, I will try on this approach.

Final thought : LookML approach needs a deployment. I am looking something which is handy for users at dashboard level. So user on the fly can create order_values and apply on result.

8

Hmm, then Dawid’s approach is better since it uses table calcs/custom dimensions (though it’s quite difficult for an end user to be expected to build a nested IF statement for every order_value). I think what you’re looking for here may not be totally possible in a user friendly way.

What’s the end goal/what is the reporting need here? I wonder if there’s another way to get to the same end state.

9

FWIW, we have a similar (possibly more complex) use-case, that also needs custom sorting on the front-end (ie not in LookML). Logged a product request here: https://portal.feedback.us.pendo.io/app/#/case/38609

We’re currently using an approach similar to Dawid’s suggestion by allowing the users to create a custom dimension on the dashboard itself, but this can be very difficult for users to maintain and we generally end up building the dashboard for them because of the complexity of dealing with a large if/else statement.

10

Hi there, this is how I would create your custom order or I call this force sorting.

#Your regular dimension 

dimension: subject {
 type: string 
 sql: ${subject} ;;
 order_by_field: subject_ordering
} 

#your force sorting dimension 

dimension: subject_ordering { 
 type: number
    sql:
      CASE
        WHEN ${subject} = 'Regular' THEN 1
        WHEN ${subject} = 'Women' THEN 2
        WHEN ${subject} = 'Men' THEN 3
        ELSE 4
      END ;;
    hidden: yes
    description: "This dimension is used to force sort the subject dimension."
  }

Thanks!

Check out these for more references:

https://docs.looker.com/reference/field-params/order_by_field

https://docs.looker.com/reference/field-reference/dimension-type-reference#tier

1 Like
11

Cheers, @Dawid . This was exactly what I needed ?