How order_by_field parameter works?

[AdrianaVela]

Hello, Im using order_by_field parameter with colours parameter in a dimension. This dimension has age groups values (string) and I want to order by 18 - 25, 26 - 30, 31 -35… So I have created a new dimension using a case when statement to give an order for each age group value, so would look something like this:

age_group_order:
sql: CASE WHEN ${age_group} = ‘18-25’ THEN 1 WHEN ${age_group} = ‘26-30’ THEN 2
WHEN ${age_group} = ‘31-35’ THEN 3 WHEN ${age_group} = ‘36-40’ THEN 4
WHEN ${age_group} = ‘41-50’ THEN 5 ELSE 6 END

The problem is when I use my age_group dimension in a chart the values (18-25, 26-30…) do not respect that order even if the dimension has already de parameter order_by_field: age_group_order

Any suggestion here?

[conor (Conor O'Mara)]

Hi Adriana!

Could you share more details of the problem. I recreated your situation to test the sorting functionality. Here’s what I set up:

Created 5 buckets to bin ages into

  age_bin:
    sql: ${omni_dbt_ecomm__users.age}
    bin_boundaries: [ 13, 31, 50, 68, 87 ]
    label: Age Bins
    description: Age of the user
    order_by_field: age_bins_order

then but some custom sorting logic into another field called age_bins_order field like

  age_bins_order:
    sql: case when ${omni_dbt_ecomm__users.age_bin}='>= 13 and < 31' then 1 when
      ${omni_dbt_ecomm__users.age_bin}='>= 31 and < 50' then 2 when
      ${omni_dbt_ecomm__users.age_bin}='>= 50 and < 68' then 3 when
      ${omni_dbt_ecomm__users.age_bin}='>= 68 and < 87' then 4 when
      ${omni_dbt_ecomm__users.age_bin}='87 and above' then 0 end
    label: Age Bins Order

Then every time I sort the age_bin field in the results section of the tab, it gets ordered in either ascending order by the age_bins_order logic. You can see it in the below screenshot ( you do not need to include column B, I’m just doing it to show you the sort is being adhered to).

And the corresponding graph obeys the sort too.

Could you walk me through how your setup is configured? I want to make sure I’m not missing any nuances in your specific implementation. The key step is sorting in the results section.

[AdrianaVela]

Hi Conor, thanks for your response!

Here are more details of my issue:

This is the definition of the dimension (screen shot below show values)

age_group:
    sql: '"AGE_GROUP"'
    order_by_field: age_group_order
    colors:
      conditions:
        - condition:
            is: 50+
          color: "#EXXXXX"
        - condition:
            is: 18-25
          color: "#EXXXXX"
        - condition:
            is: 26-30
          color: "#XXXXX"
        - condition:
            is: 31-35
          color: "#XXXXX"
        - condition:
            is: 36-40
          color: "#XXXXX"
        - condition:
            is: 41-50
          color: "#XXXXX"
      else: "#XXXXX"

And I have created a dimension to use in order_by_field. The definition is here:

age_group_order:
 sql: 
     CASE WHEN ${age_group} = ‘18-25’ THEN 1 
          WHEN ${age_group}  = ‘26-30’ THEN 2   
          WHEN ${age_group} = ‘31-35’ THEN 3
          WHEN ${age_group}  = ‘36-40’ THEN 4 
          WHEN ${age_group}  = ‘41-50’ THEN 5 
          ELSE 6 END
         format: number

If we use both of course it worked but then in the chart using, date field + age_group + measure does not respect the order, the legend stay in this order by default.

[AdrianaVela]

Hi Conor,

I found the reason, and it’s that I thought the order_by_field parameter automatically sorts the results without having to do order by on the results tab, but I understand that it is also necessary to sort them. Thanks for your feedback and for responding!

[conor (Conor O'Mara)]

@conor

The key step is sorting in the results section.

Yep brilliant that you got sorted. Yes that’s the key step to initiate the sort be order selecting “Sort ascending/descending” or click on the header. Great that we got you sorted and thanks for using the community