Timelion. Different timefield ranges for each query

Elastic Stack Kibana
1

I have these records for 1961-2016 period. I have plotted the data corresponding to the 7th month for the entire period, together with the average aggregation for the same period.

Query:

.es(q="month:7", index=1961-2016*, timefield="@timestamp", metric='avg:T').label("Values each year from 1961 to 2016"), .es(q="month:7", index=1961-2016*, timefield="@timestamp", metric='avg:T').aggregate('avg').label("Average 1961-2016")

Plot:

New%20Bitmap%20Image%20(2)
New Bitmap Image (2).jpg1920×1040 109 KB

But what I would actually want to do is to compare the evolution through the entire period with the average of a smaller timerange - 1961 TO 1990

This is what I tried (query):
.es(q="month:7", index=1961-2016*, timefield="@timestamp", metric='avg:T').label("Values each year from 1961 to 2016"), .es(q="month:7 AND year:[1961 TO 1990]", index=1961-2016*, timefield="@timestamp", metric='avg:T').aggregate('avg').label("Average 1961-1990")
And this is what I'm obtaining (plot):

New%20Bitmap%20Image%20(2)
New Bitmap Image (2).jpg1719×784 89.1 KB

Changing the query from q="month:7 AND year:[1961 TO 1990]" to q="month:7 AND year:[1961 TO 2016]" displays correct. So the problem would be that the average aggregation is calculate by dividing it to the number of years in the whole timespan, not to the number of years from the query I added.

Is there any way of accomplishing what I want? Thank you,

2

Found something that looks like what I need here: https://discuss.elastic.co/t/how-to-create-time-range-conditions-on-timelion/119404 but could not make it work. Not sure how the query should look like.

I changed the timelion:es.timefield Kibana advanced setting (Management -> Advanced Settings) with the string insert_date and then to other timestamp field I have - DATM, but did not manage to get anything.

I tried this query:
.es(q="month:7 AND DATM:[2016/10/01 TO 2016/12/31]", index=1961-2016*, timefield="DATM", metric='avg:T').label("Values each year from 1961 to 2016"),
.es(q="month:7 AND DATM:[1961/01/01 TO 1990/12/31]", index=1961-2016*, timefield="DATM", metric='avg:T').aggregate('avg').label("Average 1961-1990")

But does not near the first representation.

3

Unfortunately I don't think this is possible currently. In theory you could null out 0s if .if(eq,0,null), though that would depends on you knowing for sure that there are no 0 years, and rather a 0 represents an excluded year.

The crux of the issue is that the aggregate() function is treating null as 0. You'd need to write a new function that doesn't do that.

You could also consider filing a bug, it seems like aggregate shouldn't treat null as 0