How to visualize division of two sum aggregations per time bucket

Elastic Stack Kibana
1

Hello,

We want to create a visualizations in which we can compare the average prices per week for different years. Normally we would just get the mean prices per week. But, the problem in our case is that each document has a different count/amount of products. So to get the actual average price of all the documents per week, a weight based on the amount of products should be added to the average price of each document.

To 'simplify' this, we came up with the approach of dividing the sum of the turnover of all the docs by the sum of the amount of products of these docs to get the average price per week (per auction place/grading condition). In a TSVB this is possible by adding this in a bucket script, but as far as I know, not in a standard line chart. We would really appreciate it to visually compare week prices (per auction place/grading condition) for different years, which is however not possible in a TSVB (only time on x-axis instead of week numbers).

So, do you have suggestions on how to add this in a line chart. Could this be done in the 'advanced JSON input' or is it possible to create this in a scripted field? Thanks in advance!

2

I also tried to use a Vega visualization to solve this. However, I could not manage to get the buckets (especially the first 'groupbyauctionplace') working. Any suggestions?

{
  $schema: https://vega.github.io/schema/vega-lite/v2.json
  data: {
    url: {
      index: <my_index>
      body: {
        size: 0
        _source: ["date", "turnover", "amount", "week", "auctionplace"]
        query: {
          match_all: {}
        }
        /* aggs: {
          groupbyauctionplace: {
            terms: {
              field: "auctionplace.keyword"
              size: 6
              min_doc_count: 0
            }*/
            aggs: {
              groupbyweek: {
                terms: {
                  field: "week"
                  size: 1000
                }
                aggs: {
                  avgweek: {
                    avg: {field: "week"}
                  }
                  sumturnover: {
                    sum: {field: "turnover"}
                  }
                  sumamount: {
                    sum: {field: "amount"}
                  }
                  avgpriceweek: {
                    bucket_script: {
                      buckets_path: {
                        "sumturnover": "sumturnover",
                        "sumamount": "sumamount"
                      },
                      script: "params.sumturnover/ params.sumamount"
                    }
                  }
                }
              }
            }
          // }
        // }
      }
    }
    format: {property: "aggregations.groupbyweek.buckets"}
  }
  
  mark: line
  encoding: {
    x: {field: "avgweek.value", type: "quantitative"}
    y: {field: "avgpriceweek.value", type: "quantitative"}
    "color": {"field": "auctionplace", "type": "nominal"}
  }
}
3

I think this should be possible with the new Lens visualizations if you can't get it to work with Vega.

4

That looks like a good option. However we are currently using version 7.4.0 so this is not available for me yet. Do you have some suggestions on the JSON above, because I think it should also be possible with vega? Thanks in advance.

5

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