# Divide an aggregate field by another aggregate field
**URL:** https://discuss.elastic.co/t/divide-an-aggregate-field-by-another-aggregate-field/205288
**Category:** Kibana
**Tags:** vega
**Created:** [October 25, 2019, 3:02pm UTC](https://discuss.elastic.co/t/divide-an-aggregate-field-by-another-aggregate-field/205288 "2019-10-25T15:02:16Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@teusbenschop](https://discuss.elastic.co/u/teusbenschop)
#### Post date: [October 25, 2019, 3:02pm UTC](https://discuss.elastic.co/t/divide-an-aggregate-field-by-another-aggregate-field/205288/1 "2019-10-25T15:02:16Z")
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
---
### Author:  [@markov00](https://discuss.elastic.co/u/markov00)
#### Post date: [October 29, 2019, 10:06am UTC](https://discuss.elastic.co/t/divide-an-aggregate-field-by-another-aggregate-field/205288/2 "2019-10-29T10:06:47Z")
You have to apply two aggregations and one calculation as in the following way:
```auto
{
"$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](https://vega.github.io/editor/#/edited)
---
### Author:  [@teusbenschop](https://discuss.elastic.co/u/teusbenschop)
#### Post date: [October 29, 2019, 6:53pm UTC](https://discuss.elastic.co/t/divide-an-aggregate-field-by-another-aggregate-field/205288/3 "2019-10-29T18:53:37Z")
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:
```auto
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
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [November 26, 2019, 6:53pm UTC](https://discuss.elastic.co/t/divide-an-aggregate-field-by-another-aggregate-field/205288/4 "2019-11-26T18:53:45Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.