Selection based on duration?

[mykael (Michael Clarke)]

I've got some aggregate data that rolls up on a yearly basis. I've got real time data that feeds into one or more of the aggregated records to which I can add a field to indicating the yearly record it aggregates to.

So we have a yearly_sales index with a state_field, so we get sales_WA, sales_NSW, sales_Tas etc... and these are for a whole year - sales_WA_2020, sales_WA_2019 etc... Then we get individual sales records that get tagged according to the state and date of the sales - sales_WA_2020 for a sale that occurred this year.

My problem is with a dashboard to show yearly sales - the aggregate sales record applies to the whole year, but it either has an ingestion timestamp or a 1st of Jan timestamp on it, so if my time range is not set to include the first of the year, the yearly sales record won't show up. I'd really like the record to show up if the current range includes any part of the year (i.e. to match on just the 2020 bit and ignore the month and day). Other indexes (monthly sales roll ups) I'd probably want to be selected only by Month and Year, but to ignore day and time.

So if I set my time range to June 18th thru 21st 2020, it would still show the yearly record for 2020 and the monthly record for June on the relevant dashboards? If it became May 28th thru June 5th, it would show the Monthly records for both May and June.

Any ideas on how to do this?

[jsanz (Jorge Sanz)]

Hi @mykael, if you check the Inspect option for any dashboard panel you'll see that the dates from the time range widget are used on a range filter inside a bool query.

If I understood correctly, you'd want that main range to be inside a should section along with the other additional ranges for the January 1st. This is of course, doable adhoc using Elastic DSL and probably maybe even easier combined with some painless scripting, but I'm afraid that behavior cannot be achieved from the generic Kibana time range widget.