How to calculate the average count with term

I am looking to make a heatmap type visualization, I wanted to put the time on x-axis and the team (name) on y-axis.
a team has a number of different jobs
I wanted to put the average count with : count total / count job
but when I select average in the metrics parameter, the field does not have the field team, nor profession.
What should I do?

thanks for your help

Hi @forst ,

can you post an example document to better understand the fields data?

Also, which version of the stack are you using?

the stack's version is 7.13.4

here the data:

> "mappings" : {
>       "properties" : {
>         "@timestamp" : {
>           "type" : "date"
>         },
> "team" : {
>           "type" : "text",
>           "fields" : {
>             "keyword" : {
>               "type" : "keyword",
>               "ignore_above" : 256
>             }
>           }
>         },
>      "job" : {
>           "type" : "text",
>           "fields" : {
>             "keyword" : {
>               "type" : "keyword",
>               "ignore_above" : 256
>             }
>           }
>         },
>         "externe" : {
>               "type" : "boolean",
>               "doc_values" : false
>             }
> }

i would have the result :
average of a team that works as a dev : 58% work as dev
60% works in externally

If you want to show more than one metric per team, per Heatmap cell, you probably need to create two distinct visualizations: for instance, one per internal and the other one per external.

In your current stack version you have to probably use something like Vega to build a custom heatmap visualization with all the calculation you're looking for.

From Kibana 7.15 you have few more options with the Lens editor, which includes a Heatmap chart type, the filtered metric type and Formulas.

Having the newer Lens version you can do:

  • Pick the Heatmap visualization (still experimental)
  • on the Horizontal Axis you can put a date histogram, which means drop the "@timestamp" field
  • on the Vertical Axis drop the "team.keyword" field, or configure a "Top Values" dimension for the field "team.keyword"
  • On the Cell value you could select the Formula tab and type count(kql="external: false") / count() to check how much work is spent internally

Here's an example using the sample data logs dataset, where I've used a filtered formula:

Does it look like what you are looking for?

Hi @Marco_Liberati
thanks for your answers.
I unfortunately cannot update to kibana 7.15.
I don't know Vega.
Are there any examples of how to use Vega with the mean computation with heatmap other than the one in the documentation?

If you cannot upgrade the only option is Vega then.

In the official documentation you can find many examples on how to use it: Example Gallery | Vega-Lite

@Marco_Liberati

I saw the documentation but I do not understand how to put the name of a team in the y axis

While this blog post is not official from Elastic, you may find it useful to understand how to build a Vega heatmap step by step in Kibana: Awesome Visualizations with Kibana & Vega