Weighted Average in a DataTable of Kibana

[Henrique_Santiago]

I have this index:

PUT sales
{
  "settings": {
    "index": {
      "number_of_shards": 3,
      "number_of_replicas": 0
    }
  },
  "mappings": {
    "properties": {
      "@timestamp": {
        "type": "date"
      },
      "@version": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      "product": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      "volume": {
        "type": "float"
      },
      "cost": {
        "type": "float"
      }
    }
  }
}

POST sales/_doc
{
  "product": "Product 1",
  "volume": 4,
  "cost": 3
}


POST sales/_doc
{
  "product": "Product 1",
  "volume": 10,
  "cost": 3
}

I want to calculated the cost average based on volume.

If I have this 2 docs, I have to calculate like this:
=(sum(cost * volume))/sum(volume)
=(3 * 4 + 3 * 10) / 10 +4

How can I do this on a kibana Data Table?

I want the table like this:

Product_____| Average
Product 1 | 8.2

[cjcenizal]

Hi Henrique, I think solving your problem will be a two-step process.

1. You'll need to create a per-document field for cost * volume, e.g. a field called volumeCost. You could create this field two ways, either by reindexing your data and adding the field with a Painless script, or by creating a scripted field that calculates this value on the fly. The first option is a little more performant when querying your data but the second option might be easier.

2. I think you can create a Timeseries visualization in the Visualize app to generate the table you want. In the screenshot below, imagine the first aggregation for products.base_price is actually a reference to the volumeCost field and the second aggregation for products.quantity is actually a reference to the volume field. This way you can define a bucket script that performs the calculation you want, and order the results by product ID or name in a table.

[cjcenizal]

Also, if you're interested in tracking progress for supporting bucket scripts in our other visualization types, please follow this issue: https://github.com/elastic/kibana/issues/4707.