Question

Custom Sort on custom dimension

  • 29 April 2020
  • 9 replies
  • 2550 views

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.


9 replies

Userlevel 6
Badge +1

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)
)
)

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.

Userlevel 7
Badge +1

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.

@izzy,


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}

Userlevel 7
Badge +1

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_Nawrot’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

Thanks @izzy,


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.

Userlevel 7
Badge +1

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.

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.

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 

Reply