Values changing when ordering by metric

Hi,

I am facing an issue with "order by". I have an Y axis with an "average" aggregation. I have an X axis with a "Term" aggregation (where my terms are street names) => I have an average value for each street.
I would like my X axis to be sorted by the average aggregation. I thought it was working when I selected "order by metric: [my aggregation]", but when I set a filter to display only one street, the average value of this street changes. This does not happen when I use "order by: alphabetical": (in this case the values stay the same).

Here is an example.

  1. Using order by: alphabetical

  2. Then filtering on a random street... the value is still 4.399

  3. Now ordering by metric: average

  4. Then filtering on a random street => the value changes from 2.337 to 4.135

There is probably something that I am misunderstanding on how the order by works with aggregation... Any help would be very appreciated :slight_smile:

Thanks

Johanna

Hi @Jomy ,

welcome to the Kibana community.

The Order by input in the screenshots is used to tell Kibana how to rank the results: when using anything different from Alphabetical the "top" terms will change based on the filters used.
Using an alphabetical order is stable, but you only have a partial result, for instance you may not see the "top" terms for that metric.

To explain on your pictures:

  • In the alphabetical case you have the first 10 streets sorted by name with their average score shown. Filtering will rerun the aggregation limited to that street, which returns that street average value, 4399.

  • in the average metric case you have the lowest 10 streets by average value, but then when you filter on a single street name, you probably picked a street outside that 10 results and it will have a higher value.

Can you filter in both case the same street name and confirm that is always the same value?

Hi @Marco_Liberati ,

Thanks for your answer.
When picking a specific street, it has the same value when using an alphabetical order with and without filtering on this street, and when using metric order with filtering on this street only.
The only case where the value of this street changes is when I display all the streets sorted by metric.

I also realized that this happens only when I set a wide timerange. I know that by default Elasticsearch only returns a certain number of documents when calling the API. I wonder if the order by metric recomputes the average values, only taking account of the n first documents.

It computes the metric first (filters may apply) and then orders them to pick the Top N

@Marco_Liberati okay, so if I understand correctly, "order by" does not do anything else than ordering the values.
In this case, do you have an idea on why the values would change when ordering by metric?