# Time builder: average of a year compared to the long term average

**URL:** <https://discuss.elastic.co/t/time-builder-average-of-a-year-compared-to-the-long-term-average/166600>\
**Category:** Kibana\
**Created:** [January 31, 2019, 3:58pm UTC](https://discuss.elastic.co/t/time-builder-average-of-a-year-compared-to-the-long-term-average/166600 "2019-01-31T15:58:00Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![michimau](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/michimau/32/23262_2.png) [@michimau](https://discuss.elastic.co/u/michimau)\
**Post date:** [January 31, 2019, 3:58pm UTC](https://discuss.elastic.co/t/time-builder-average-of-a-year-compared-to-the-long-term-average/166600/1 "2019-01-31T15:58:00Z")

</div>

Dear all,

I have a data series of numerical observations.  
I would like to create a metric with time builder, which shows:  
(sum of obsValues filtered for 2018) / (the average of (sum of obsValue by years)) \*100

my data layout is:  
"date","year","obsValue"  
"2011-01-01 00:00:00",2011,55743  
complete file in: [https://gist.github.com/michimau/d94b9adb23e4b870b1e7534051d3b1af](https://gist.github.com/michimau/d94b9adb23e4b870b1e7534051d3b1af)

I am breaking my head around it, but i do not see how it could possibly work.  
Any idea if this is possible in the first place?

Best!  
M

---

<div class="post-metadata">

**Author:** ![michimau](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/michimau/32/23262_2.png) [@michimau](https://discuss.elastic.co/u/michimau)\
**Post date:** [February 12, 2019, 9:18pm UTC](https://discuss.elastic.co/t/time-builder-average-of-a-year-compared-to-the-long-term-average/166600/2 "2019-02-12T21:18:59Z")

</div>

no lta?

---

<div class="post-metadata">

**Author:** ![Marius\_Dragomir](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marius_dragomir/32/42087_2.png) [@Marius\_Dragomir](https://discuss.elastic.co/u/Marius_Dragomir)\
**Post date:** [February 22, 2019, 10:05am UTC](https://discuss.elastic.co/t/time-builder-average-of-a-year-compared-to-the-long-term-average/166600/3 "2019-02-22T10:05:58Z")

</div>

I think you can make this work if you use a Filter Ratio aggregation in TSVB.  
Set the Aggreation to Filter Ratio  
Set the Numerator to year:2018, the Denominator to \* (the denominator is that by default)  
Set the Metric to Sum of field obsValue.  
Now create a Math aggregation where you can set the value from the previous aggregation as a parameter that you can divide by the number of years (sadly you'll have to set that by hand, because I don't think you can get it from an aggregation) in order to get the average by year and then you can multiply it with 100 as well. This will you give you a chart with the values that you desire.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [March 22, 2019, 10:06am UTC](https://discuss.elastic.co/t/time-builder-average-of-a-year-compared-to-the-long-term-average/166600/4 "2019-03-22T10:06:01Z")

</div>

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