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
1 on this part of the feature. Especially for table data.