Order by Another Dimension

It is frequently needed to order dimension elements other than alphabetically. To avoid having to concatenate a numeric field, it would be very useful to be able to specify an “Order By” dimension.

Sometimes it is useful for dimensions to be ordered in a different way to numerical/alphabetical. A separate dimension that is used for ordering which is referenced in the master dimension would then be useful.

Hi @Alex_H, I don’t fully understand what you suggested. Do you mean to say:

Sometimes you want to order the values of dimension by a special rule (rather than ASC or DESC). And you suggest to add an attribute to specify this ordering.

A stripped down, non-practical example would be:

dimension some_dim {
   type: 'number'
   order_by: @sql {{ #SOURCE.some_dim }} % 3;;
}

In this case the value will be sorted by their remainer of division by 3.

Is this what you have in mind? Do you have a specific example that you could share?

For example I have a text dimension which has the following categories

Category AAA
Category BBB
Category CCC

The business needs to consistently present the categories in the following order:

Category BBB
Category CCC
Category AAA

Of course we have the option to manually add numbering to the categories

Category 1.BBB
Category 2.CCC
Category 3. AAA

But then the numbering is visible everywhere, and may be used in filters. What happens, for example if the company adds category Category DDD which should be displayed between Category CCC and Category Category DDD - any references would be broken.

In a crosstab report, it would be possible to add an indepedent ordering dimension (although this would then take unnecessary space), however in charts there is no ordering dimension.

If we had an ordering dimension, this would be achievable e.g.

dimension category {
    label: 'Category'
    type: 'text'
    hidden: false
    definition: @sql {{ #SOURCE.CATEGORY }};;
    ordering_dimension: {{ category_order }}
}

dimension category_order {
    label: 'Category Order'
    type: 'number'
    hidden: true
    definition: @sql {{ #SOURCE.CATEGORY_ORDER }};;
}
2 Likes

Interesting problem, thanks for sharing!

The above approach is something we definitely aim to support in the future, and the scope can be extended to “Order by fields” which will support “Order by measures” as well.

Just sharing, another approach for self-service users is to allow data exploration sorted by columns that are not dragged in the visualization settings.

Let’s say we have the Categories table below

order cat cat_id
1 Mobile 132
2 PC 99
3 Tablet 12

Then add Category Order to Sort section of Visualization Settings

Thanks @anthonytd - great to know it’s on the radar. In the version of Holistics I’m using, it doesn’t allow me to add fields that are not used in the analysis to the order by field. Moreover, ATM order by fields can only be applied to Table and Pivot visualisations.

Hi Alex,

1) Sort a field by another field that does not exist in Visualization Settings
This feature is already under our radar. I will keep you up-to-date on its progress.

2) Sort in charts
The sorting function in charts might be slightly more hidden than one in Tables and Pivot Tables. You can still sort the chart using the Hamburger menu on the top right corner.

However, please note that the sort for charts will be applied after row limit is applied (we’ve written extensively about it in this document: Sorting Data | Holistics Docs)

1 Like