Aggregation by Maximum Date Doesn't Work Well

[Carlos.limaElaw (Carlos Henrique)]

Hi, there!

I'm trying to sort a vertical bar Chart by a agregation of maximum date like this:

"cc_mes_ano_registro" is a scripted field that returns the Month
and Year of a date field(data_de_registro) as a string (because string fields are aligned in the center of bars as you can see in "December - 2018"), but there's a problem:

As you can see, March, July and August - 2018 are ahead of July 2019.
In other words, it is not ordered by the maximum date in each bucket.

And if I order by descending it goes like this:

It's completely different.

This is the "cc_mes_ano_registro" script:

if(doc['data_de_registro'].size() > 0 ){
  def registroMillis = doc['data_de_registro'].value.getMillis();
  ZoneId timeZone = ZoneId.of(ZoneId.SHORT_IDS.get('BET'));
  LocalDate dataRegistro = 
  LocalDateTime.ofInstant(Instant.ofEpochMilli(registroMillis),timeZone).toLocalDate();

  def mesAno = dataRegistro.getMonth()+" - "+dataRegistro.getYear();
  return mesAno;
}
return "N/A";

Some solution, something wrong that I can't see or it's just a bug?

Thank you!

[nickpeihl]

Is there a reason you are not using a Date Histogram aggregation? It might make this much easier without the need for a scripted field.

[Carlos.limaElaw (Carlos Henrique)]

Hi, Nick! Thanks for response me.

I'm using a scripted field because Date Histogram put the label in the bar's corner and I need in the middle.

[nickpeihl]

HI Carlos,

I see now. We have an enhancement request for this already. https://github.com/elastic/kibana/issues/38494

You can use the Inspect button on your Visualization to see the results of the query. You can also change the View from Data to Requests to see the request and response sent to/from Elasticsearch. This might offer a clue why the ordering is not what you are looking for. https://www.elastic.co/guide/en/kibana/current/vis-inspector.html

[Carlos.limaElaw (Carlos Henrique)]

I discovered something!

My field local_trabalho.keyword have some null data, and this is breaking the axis order.
So I did a scripted field that returns "N/I" for null values and solved the problem.

Thanks for answer me anyway!