Support for Complex Formulas

[infosecwatchman]

Kibana version:
8.3.2

Elasticsearch version:
8.3.2

Server OS version:
docker running on Oracle Linux Server 8.6

Browser version:
Brave 1.42.88

Original install method (e.g. download page, yum, from source, etc.):
docker-compose

Describe the bug:
Not sure if this a bug or feature request.
Running formulas on the y-axis does not appear to be running on all filtered data, and therefore not sorting properly. And since the formula is not processing all data, it sorts not on the y-axis, but the x-axis (ie. the first ip in alphanumeric order).
So I'm importing Tenable.io information, and I'm trying to create a vertical bar graph, with ip as the x-axis, and sort on highest "weighted" score for vulnerability severity using this formula:
((sum(vpr.score, kql='vpr.score >= 9') * 0.40) + (sum(vpr.score, kql='vpr.score >= 7 and vpr.score < 9') * 0.15) + (sum(vpr.score, kql='vpr.score >= 4 and vpr.score < 7') * 0.05)) / ((count(kql='vpr.score >= 9') * 0.40) + (count(kql='vpr.score >= 7 and vpr.score < 9') * 0.15) + (count(kql='vpr.score >= 4 and vpr.score < 7') * 0.05))

Excel equivalent:
=SUMPRODUCT("severity_score","appropriate weight percentage")/SUM("sum of weight percentages")

[elasticmachine]

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

[flash1293]

Duplicate of #114951 - this is a known issue, and we are working on a fix, but it won't land in the near term.

Some mitigations for the time being:

In the next minor 8.4 which will be released in the near future we added a way to change the ranking function of the "Top values" dimension: #134811

It's not a perfect replacement, but in some situations it's possible to get the relevant terms to show up by ranking by a "proxy" and increasing the number of top values. In your case it looks like ranking by average of vpr.score and top 20 values or so will give you more relevant results. As described in the linked issue, another way to mitigate is to switch to the table vis, increase the number of top values to 500 or so and then sort the column on the client side - this way the most relevant values should be shown on the top.