How to calculate the average count with term

[forst]

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

[Marco_Liberati]

Hi @forst ,

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

Also, which version of the stack are you using?

[forst]

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

[Marco_Liberati]

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?

[forst]

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?

[Marco_Liberati]

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

[forst]

@Marco_Liberati

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

[Marco_Liberati]

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