Sum of Average aggregation in visualization

[umay_fb]

i using this script to get sum of average aggregation

Script:

{
  "size": 0,
  "aggs": {
    "by category": {
      "terms": {
        "field": "spv.keyword"
      },
      "aggs": {
        "by user_id": {
          "terms": {
            "field": "sales.keyword"
          },
          "aggs": {
            "avg by user": {
              "avg": {
                "field": "target"
              }
            }
          }
        },
        "average by user, category": {
          "sum_bucket": {
            "buckets_path": "by user_id>avg by user"
          }
        }
      }
    }
  }
}

the value i get is

Value:

  "aggregations" : {
    "by category" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [{"key" : "SPV1",
          "doc_count" : 13265,
          "by user_id" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "SALES1",
                "doc_count" : 5184,
                "avg by user" : {
                  "value" : 30.0
                }
              },
              {
                "key" : "SALES2",
                "doc_count" : 2407,
                "avg by user" : {
                  "value" : 30.0
                }
              },
              {
                "key" : "SALES3",
                "doc_count" : 2244,
                "avg by user" : {
                  "value" : 30.0
                }
              },
              {
                "key" : "SALES4",
                "doc_count" : 1977,
                "avg by user" : {
                  "value" : 30.0
                }
              },
              {
                "key" : "SALES5",
                "doc_count" : 1446,
                "avg by user" : {
                  "value" : 30.0
                }
              },
              {
                "key" : "SALES6",
                "doc_count" : 7,
                "avg by user" : {
                  "value" : null
                }
              }
            ]
          },
          "average by user, category" : {
            "value" : 150.0
          }
        },
        {
          "key" : "SPV2",
          "doc_count" : 9416,
          "by user_id" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "SALES7",
                "doc_count" : 3955,
                "avg by user" : {
                  "value" : 30.0
                }
              },
              {
                "key" : "SALES8",
                "doc_count" : 2655,
                "avg by user" : {
                  "value" : 30.0
                }
              },
              {
                "key" : "SALES9",
                "doc_count" : 1701,
                "avg by user" : {
                  "value" : 30.0
                }
              },
              {
                "key" : "SALES10",
                "doc_count" : 1105,
                "avg by user" : {
                  "value" : 30.0
                }
              }
            ]
          },
          "average by user, category" : {
            "value" : 120.0
          }
        }

how can i create a visualization in kibana which has value average by user,category like :

SPV Value
SPV1 150
SPV2 120

Thanks...

[jsanz (Jorge Sanz)]

You can do that with the Table widget from Visualize app.

Using the Kibana Sample Flight Data, I got these two tables. On the left you got the different destination countries, along with the sum of the average Distance in Miles by Carrier.. On the left, to check it's correct, the table shows the average distance by carrier, filtered for the first country in the left.

And here you have how the table on the left was configured.

Buckets

A simple term aggregation by destination country to split the table rows

Metrics

And for the metric part you need to first select a Sum Bucket where the buckets are defined as a Terms aggregation by Carrier and for each bucket you'll compute the Average of the distanceMiles field.

I hope this is clear enough, let us know what you think.