How to aggregate values by term to make an avarage of the result set?

I would like to make an aggregation (or "sql group by" like action) to a index, so the result can be used to make a line chart of the average value of that result set, like this
-Index data:
|attendance id|clerk id|hours spent|year |
|1 |A |1.5 |2015|
|1 |B |2 |2015|
|2 |B |3 |2015|
|3 |C |6 |2016|
|3 |B |3.2 |2016|
|4 |A |7 |2017|

The line graph, when plotted, would have 4 dots with the years and man-hour values:
2015: 3.25 [(3.5+3)/2]
2016: 9.2 [(9)/1]
2017: 7 [(7)/1]

Is there a way to accomplish this?

Yes, in Visualize:

  • Set your metric to average of hours spent
  • Add a bucket that's a Terms Agg on clerk id
  • Add a bucket that's a Terms Agg on year (unless year is a date field, then use Date Histogram)

That will show you the average hours per clerk, per year, which I think is what you are asking for here.

Hi @Joe_Fleming, thank you for your answer.
In fact I'd like to have an avarage hour per year, not year and clerk.
I could create a new index with the agretation already done by attendence id, but I'm trying to find out if there is another way of doing it.

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