[Lens] Allow client-side sorting of dimensions and legends at datasource level for all chart types

[wylieconlon]

Primary capabilities we expect from this feature

1. Sorting data by metrics when underlying API does not offer a sort (see cases below)
2. Legend sorting

User story

Sort order affects the understanding of the data, but not all charts allow custom sorting. This is a frequently-requested feature when users run into one of the aggregation limits of Elasticsearch. Here are some example use cases and limitations:

Sorting data by metrics / methods underlying API does not offer

1. Metric sort - Changing the filter order based on the count or other metric
2. Metric sort, percentiles - Sorting terms by percentiles
3. Metric sort - formula/columnwise math: Sorting based on a calculation, for example sorting by derivative
4. Explicit sort Assigning a predefined order to Terms based on a list of known terms
5. Offering sort using both axis - Sorting both axes of a heatmap by ascending order, not just one
6. Sorting on both categorical groupings independently. Occurs in 1) heatmaps 2) mosaic 3) grouped bars 4) pie/donut

Sorting the legend separately from visualization

1. Legend metric sort - sorting the vis on metric but legend alphabetically
2. Explicit legend sort - assigning a predefined order to Terms based on a list of known terms

Example before and after

This visualization is using math to calculate the "max - min" value per bar. The natural sort order is not helpful, so we actually want to apply client-side sorting based on the number displayed:

Proposed functionality

Each dimension will offer an advanced setting that does client-side sorting, visible in the flyout editor. This setting would be called "Sort override" and have the options "No override", "Ascending" and "Descending". Numbers and dates would be sorted based on the raw value, and all other types would be sorted based on the formatted value.

[elasticmachine]

Pinging @elastic/kibana-app (Team:KibanaApp)

[gimmic]

I ran into this use-case today when doing a bar visualization with filter buckets on resulting counts.

An option to sort(client/render side is fine) would be nice. As a user, I'd expect this as an option under 'panel'.

Note the Legend options already include an "order buckets by sum"; a similar type of option could be offered for the actual visualization display itself.

[flash1293]

cc @MichaelMarcialis I wonder whether this would also handle the table use case you had in mind

[wylieconlon]

Got another use case example from discuss: the use case is to take the "largest 20 values, but display them in ascending order". This can only be done by applying the sort order after we receive the data from Elasticsearch.

[MichaelMarcialis]

I wonder whether this would also handle the table use case you had in mind

Forgive the potentially silly question on my part, but how does this differ from the current Order by and Order direction options in Lens?

Regarding the table row/column sorting options I was playing around with, I think that scenario is unique as there may be multiple row/column dimension items present, which then increases the complexity of the opposite dimension group's sorting options. But if I'm oversimplifying, let's plan to chat further after the holidays.

[wylieconlon]

@MichaelMarcialis The "top values" aggregation is doing sorting in a unique way, which is documented by Elasticsearch. It's not meeting our user's needs though, as shown in the 5 examples that I've listed in the issue description. That's why I've proposed a sort override feature which would let us provide what users are asking for.

[ghudgins]

Related discuss issue: https://discuss.elastic.co/t/sorting-in-kibana-data-table-visualization/274200/1

[ghudgins]

might want to consider this option on table even though you can use the column sorting options today

tried sorting it on the right hand side of the editor where i told it to make a count of records

[ghudgins]

+1 - a case for sorting semver

[ghudgins]

Related issue #17532

[albertosatec]

There are any deadline for this issues?