Avg bucket aggregation over date histogram returns incorrect value when having empty bucket

Elastic Stack Elasticsearch
1

Hello all, we build the below query to get the average value of the sales aggregation in the date histogram but we find the value returned seem incorrect. We use extended_bound to show the empty buckets so that the avg bucket will calculate the average value of the total monthly sales within the date range.

      "aggs": {
        "sales_over_time": {
          "date_histogram": {
            "field": "orderTime",
            "interval": "month",
            "min_doc_count": 0,
            "extended_bounds": {
              "min": "2019-06-29T14:05:33.543+08:00",
              "max": "2019-07-04T14:05:33.543+08:00"
            }
          },
          "aggs": {
            "salesorderline": {
              "nested": {
                "path": "salesOrderLines"
              },
              "aggs": {
                "sales": {
                  "sum": {
                    "field": "salesOrderLines.grossAmount"
                }
              }
            }
          }
        }
      },
      "avg_monthly_sales": {
        "avg_bucket": {
          "buckets_path": "sales_over_time>salesorderline>sales"
        }
      }
      }
      }

Below is the result:

"aggregations": {
    "sales_over_time": {
      "buckets": [
        {
          "key_as_string": "2019-06-01T00:00:00Z",
          "key": 1559347200000,
          "doc_count": 0,
          "salesorderline": {
            "doc_count": 0,
            "sales": {
              "value": 0
            }
          }
        },
        {
          "key_as_string": "2019-07-01T00:00:00Z",
          "key": 1561939200000,
          "doc_count": 17,
          "salesorderline": {
            "doc_count": 18,
            "sales": {
              "value": 24015629
            }
          }
        }
      ]
    },
    "avg_monthly_sales": {
      "value": 24015629
    }
  }

I think avg_monthly_sales should be 24015629/2 ...

1 Like
2

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