How can I select the latest state of the data?

[KibanaUse]

Hello all,

I have a list of data and I am trying to filter my data to get a count. I am going to use a metric as the visualization.

Attached, there is a data table of my data. I keep a log of my customers each time their state is updated. I want to get the count of the all customers with latest state red.

According to the table here, there are two entries with red state, however since Ingolstadt Clinical Center turned to green later(according to its time stamp, green comes later than red), its current state is green.

So the result should be 1. How can I achive to get the count?

Thanks in advance!

I am using version 6.1

[thomasneirynck]

hi @KibanaUse,

This would require you to run two aggregations in series, getting the last value per customer, and then counting those up. That's not supported in Kibana-editor right now.

If you're satisfied with a half-solution, you can run the first part using a top-hits.

You can compute the last status for every individual customer and display that in a table.

It'd look something like:

But you can't do that second step, collapse them all into a single value and count the results.

A possible alternative (and I'm not 100% sure of this) would be to use a Vega visualization (available in Kibana 6.2, cf. https://www.elastic.co/blog/custom-vega-visualizations-in-kibana).

If you can express the result of your computation into a single query to Elasticsearch (and I would check the Elasticsearch forum for that, you'll likely need some custom scripting), you can copy paste that query directly into the Vega-editor. Then you can visualize that value using the Vega visualization language.