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

wylieconlon commented 3 years ago

Primary capabilities we expect from this feature

1) Sorting data by metrics when underlying API does not offer a sort (see cases below) 1) 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

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

Sorting the legend separately from visualization

Legend metric sort - sorting the vis on metric but legend alphabetically
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:

Screen Shot 2020-12-16 at 3 30 28 PM

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 commented 3 years ago

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

gimmic commented 3 years ago

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 commented 3 years ago

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

wylieconlon commented 3 years ago

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 commented 3 years ago

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 commented 3 years ago

@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 commented 3 years ago

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 commented 3 years ago

+1 - a case for sorting semver

ghudgins commented 2 years ago

albertosatec commented 2 years ago

There are any deadline for this issues?

elastic / kibana