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

[michimau (Mauro Michielon)]

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

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

[michimau (Mauro Michielon)]

no lta?

[Marius_Dragomir]

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.