Divide an aggregate field by another aggregate field

[teusbenschop]

Hi,

I am trying get the average price of quantities of items sold for a given price.

Here is the data:

"data": {
    "values": [
      {"week": "1", "turnover": 200, "quantity": 100},
      {"week": "1", "turnover": 250, "quantity": 110},
      {"week": "1", "turnover": 200, "quantity": 100},
      {"week": "2", "turnover": 220, "quantity": 100},
      {"week": "2", "turnover": 270, "quantity": 120},
      {"week": "2", "turnover": 220, "quantity": 100},
      {"week": "3", "turnover": 300, "quantity": 150},
      {"week": "3", "turnover": 400, "quantity": 170},
      {"week": "3", "turnover": 500, "quantity": 190}
    ]
  },

For each of the week of [1, 2, 3], I am trying to get the total turnover during that week, divided by the total quantity during that week.

During week 1, the total turnover is 650 items. The total quantity sold is 310 during that same week. So the average price would be 310 / 650 = 0,47.

How can I visualise this average price per week?
The weeks are on the X-axis, and the average price is on the Y-axis.

So far I have tried a few things, the best of which should have been this:

{

  $schema: https://vega.github.io/schema/vega-lite/v2.json

  title: Vega-Lite

  "data": {
    "values": [
      {"week": "1", "turnover": 200, "quantity": 100},
      {"week": "1", "turnover": 250, "quantity": 110},
      {"week": "1", "turnover": 200, "quantity": 100},
      {"week": "2", "turnover": 220, "quantity": 100},
      {"week": "2", "turnover": 270, "quantity": 120},
      {"week": "2", "turnover": 220, "quantity": 100},
      {"week": "3", "turnover": 300, "quantity": 150},
      {"week": "3", "turnover": 400, "quantity": 170},
      {"week": "3", "turnover": 500, "quantity": 190}
    ]
  },

  "transform": [
    {
      "aggregate": [{
       "op": "sum",
       "field": "turnover",
       "as": "newfield"
      }]
    }
    
  ],

  "mark": "point",

  "encoding": {
    "x": {"field": "week", "type": "ordinal"}
    "y": {"field": "price", "type": "quantitative"},
  }

}

That runs on a Vega-Lite visualization on Kibana.

The above is not the exact correct solution, but this JSON leads to a completely blank visualization, as if the Vega-lite code crashes.

My question is this: How should I calculate this average weekly price and visualise it?

Thank you!

Teus Benschop

[markov00 (Marco Vettorello)]

You have to apply two aggregations and one calculation as in the following way:

{

  "$schema": "https://vega.github.io/schema/vega-lite/v2.json",

  "title": "Vega-Lite",

  "data": {
    "values": [
      {"week": "1", "turnover": 200, "quantity": 100},
      {"week": "1", "turnover": 250, "quantity": 110},
      {"week": "1", "turnover": 200, "quantity": 100},
      {"week": "2", "turnover": 220, "quantity": 100},
      {"week": "2", "turnover": 270, "quantity": 120},
      {"week": "2", "turnover": 220, "quantity": 100},
      {"week": "3", "turnover": 300, "quantity": 150},
      {"week": "3", "turnover": 400, "quantity": 170},
      {"week": "3", "turnover": 500, "quantity": 190}
    ]
  },

  "transform": [
    {
      
      "aggregate": [{
       "op": "sum",
       "field": "turnover",
       "as": "totalTurnover"
      },
      {
       "op": "sum",
       "field": "quantity",
       "as": "totalQty"
      },
      {
       "op": "sum",
       "field": "quantity",
       "as": "totalQty"
      }],
       "groupby": ["week"]
    },
     {"calculate": "datum.totalQty / datum.totalTurnover", "as": "avgPrice"}
    
  ],

  "mark": "point",

  "encoding": {
    "x": {"field": "week", "type": "ordinal"},
    "y": {"field": "avgPrice", "type": "quantitative"}
  }

}

You can test and debug static visualization like yours directly in the vega online editor https://vega.github.io/editor/#/edited

[teusbenschop]

Thank you a lot Marco, for the solution to visualise the data. That information helps.

May I additionally ask what query to send to Elastic Search when I want to fetch this data from an index on Elastic Search?

The data property will then use the url, similar to this:

data: {
    url: {
      index: company_index
      body: {
        query: {
...

How should I write this query in order to get the same tabular data as in the example?

Thank you for any help!

Teus Benschop