Visualize unique count of IDs values by a date field

[H0tmilk (hotmilk)]

Hello,

This is my data (for example) in Elasticsearch :

{
	"id": 1,
	"status": "Active",
	"title": "Incident 1",
	"@timestamp": "28/04/22"
},
{
	"id": 2,
	"status": "Active",
	"title": "Incident 2",
	"@timestamp": "28/04/22"

},
{
	"id": 3,
	"status": "Active",
	"title": "Incident 3",
	"@timestamp": "28/04/22"

},
{
	"id": 1,
	"status": "Closed",
	"title": "Incident 1",
	"@timestamp": "29/04/22"

},
{
	"id": 2,
	"status": "Closed",
	"title": "Incident 2",
	"@timestamp": "29/04/22"

}

As you can see, the status of the incidents with ID 1 and 2 have been changed to "Closed" and so we have 2 entries for these incidents.

I want to make a simple pie chart where I'll have the current status of incidents.

Unfortunately, as you can see, the pie shows 5 values (3 active, 2 closed).
I would like to have 3 values displayed only (1 active, 2 closed).
How can I do that ?

TL;DR
I would like to display a unique count of incidents, considering only the incident with the most recent @timestamp field for each id.

I am using Kibana v7.17.0.

Thanks in advance !

[stephenb (Stephen Brown)]

@H0tmilk Hi and welcome to the community!

See if you can follow me on this..

There is a function called "Last value" that will do this for you but it requires a numeric to work with (not sure why I will ask at the bottom)

So here is my data set... Note I added a status_code which is an integer that corresponds to your status keyword

Here is the mapping and data set
This is in 7.17.1

DELETE discuss-unique

PUT discuss-unique/
{
  "mappings": {
    "properties": {
      "id" : {"type": "keyword"},
      "status" : {"type": "keyword"},
      "status_code" : {"type": "integer"}, 
      "title" : {"type": "keyword"},
      "@timestamp" : {"type": "date" }
    }
  }
}

POST discuss-unique/_doc
{
	"id": 1,
	"status": "Active",
	"status_code": 1,
	"title": "Incident 1",
	"@timestamp": "2022-04-28"
}

POST discuss-unique/_doc
{
	"id": 2,
	"status": "Active",
	"status_code": 1,
	"title": "Incident 2",
	"@timestamp": "2022-04-28"

}

POST discuss-unique/_doc
{
	"id": 3,
	"status": "Active",
	"status_code": 1,
	"title": "Incident 3",
	"@timestamp": "2022-04-28"
}

POST discuss-unique/_doc
{
	"id": 1,
	"status": "Closed",
	"status_code": 2,
	"title": "Incident 1",
	"@timestamp": "2022-04-29"
}

POST discuss-unique/_doc
{
	"id": 2,
	"status": "Closed",
	"status_code": 2,
	"title": "Incident 2",
	"@timestamp": "2022-04-29"
}

Then Create a Pie Chart in Lens!

Note 3 Total Incidents

Question : @ghudgins Why Does "Last value" only work on numerics.. when I try to use a keyword field it will not work... seems like last value should work on a keyword too ... bug or feature?

[H0tmilk (hotmilk)]

@stephenb thanks for you answer !

In fact it works but only for numerics fields, which doesn't seem to make sense in this case ¯\_(ツ)_/¯

I don't know if other ways of doing it exists, in particular using aggregation based visualizations.

Thanks a lot anyway !

[stephenb (Stephen Brown)]

@H0tmilk

In fact it works but only for numerics fields, which doesn't seem to make sense in this case ¯_(ツ)_/

Yup that is why I asked ... the person I asked is a "Master" so we see what he says if / when he replies.