Search code examples
kibanaelasticsearch-aggregation

Kibana Visualisation Average Monthly Sums Over Years

I have these daily records.. for a period of 7 years. I managed to calculate the sum for each month and get a chart like this enter image description here

I also managed to plot the sum on each month over the years. So the first point in this second chart represents something like this:

Sum of Daily Recods in Jan 2010+Sum of Daily Records in Jan 2011+...+ Sum of Daily Records in Jan2016

![image|690x347](upload://tQMe5QWfa5Rraqj3DHkSwHbUZFV.png)

And is correct since I checked it using Excel. But now I would like to average the total on each month over the 7 years. And this goes very wrong because when I select Average aggregation on Y-axis, the average is calculated as the sum of the daily values and split to the number of days in each month. And I need this sum divided by the number of years.. Can this be done?

This second chart would look exactly the same for the average of months over the years, except the Y-axis would have a Scale divided by 7.

Or.. is it possible to create new montly records based on the sums obtained in the previous charts?

Solution

  • I spent the whole day getting the right DSL query. But I was barking at the wrong tree. The solution is actually very short. It may not be dynamical, or at least I haven't found it yet, but here it is:

    enter image description here

    Hope it would help others.