Custom Sort on custom dimension

SaurabhD
Participant I

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.

0 10 13.8K
10 REPLIES 10

Dawid
Participant V

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

SaurabhD
Participant I

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.

For a slightly more robust solution:

order_by_field

LookML parameter: Sort a field by the values of another field.

@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.

SaurabhD
Participant I

@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}

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

SaurabhD
Participant I

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.

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 

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

Top Labels in this Space
Top Solution Authors