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

Elastic Stack Kibana
1

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?

2

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.

3

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.