How to calculate the average count with term

Elastic Stack Kibana
1

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

2

Hi @forst ,

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

Also, which version of the stack are you using?

3

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

4

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:

Screenshot 2021-09-23 at 18.17.13
Screenshot 2021-09-23 at 18.17.132174×1272 242 KB

Does it look like what you are looking for?

5

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?

6

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

7

@Marco_Liberati

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

8

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

9

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.