I have a data set that I'd like to add to Elasticsearch so that we can build visualizations with it in Kibana. The problem I am running into is that since the data is pre-aggregated average aggregations I do on this data are incorrect since they do not take into account the quantity. Here is a simplified sample of my data:
+-------+-------+-------------+--------------+---------------+
| Month | Store | Total Sales | Average Sale | Quantity Sold |
+-------+-------+-------------+--------------+---------------+
| Jan | A | $1000 | $100 | 10 |
| Jan | B | $880 | $80 | 11 |
| Feb | A | $375 | $75 | 5 |
| Feb | B | $800 | $40 | 20 |
+-------+-------+-------------+--------------+---------------+
If I do an average of the average sale column I would get $74 ((100+80+75+40)/4). The result I would like to see would take into account the quantity and I would get $66 ((100×10+80×11+75×5+40×20)/(10+11+5+20)).
Is there a way to store pre-aggregated data like this in Elasticsearch so that aggregations will work correctly similar to the way the rollups work?