[Lens] sort by a formula column without expanding the entire range

[Leaf-Lin]

Describe the feature:

I need to sort by a formula column without expanding the entire range of top hits.
Today (running 7.15), I'm able to create a Lens Table where the formula allows me to define a column of interest. But the choice of sorting on top hits are either alphabetically or the count of some fields which does not represent the column of interest.

For small data size, I'm able to expand the entire top hits range and then sort by the column using the browser sort. But when my data size grows bigger, this is not possible and the sorting becomes meaningless as it only takes the first x top hits.

I'm not 100% sure if this is related to #102956 or #94788, so I've posted in a new issue. Please feel free to close this if this is a duplicate of another issue.

Describe a specific use case for the feature:
I've provided a csv example -- status_code_sorting.csv of 100 clusters and status code received in proxy log. The use case is to create a table sorting by the rejection ratio which can be calculated as count(kql='status_code:429')/count() for each unique cluster.

Using the following Lens configuration, I can create a table giving me the correct sorting when expanding Number of values to 100 in the Top values of name.

But if my data size is much larger, I do not wish to expand the Top value of name to the entire range of data. In the example here, if I restrict my Top value of name to only 10 records, I no longer see my top highest "rejection rate" cluster_3 on the list.

[elasticmachine]

Pinging @elastic/kibana-vis-editors (Team:VisEditors)

[ghudgins]

we hope to fix this in this issue - #86184

[flash1293]

This issue is specifically about sorting by formula without fetching all rows first. I’m not sure how we can achieve this - for some use cases it’s just not feasible to stream data to Kibana before doing the sorting (e.g. if there are hundreds of thousands of terms)

[squadwuschel]

If you can't sort for the value you have created in the formula, then it's a useless feature to create custom formulars in large datasets where you want to sort for best or worst values after calculating them.

[ghudgins]

we are working on a more comprehensive solution to this across the product. issues will be created at some point soon to encompass this work and then we will close / link

[markov00]

In order to provide better transparency of priorities, issues that will not be prioritized within the next 24 months are being closed.

Tracking request in Lens general improvements ice box, solvable possibly with ESQL #184648