What is the best way to create reference values which are based on past information in Elasticsearch?
My current use case involves detailling in Kibana how the values for a day, week or month relate to past developments. Take this example using the flights data. The graph shows the last day of counts (green bars) and the average count for each time interval over the previous week (red area).
This is hacked together in Kibana lense by using the shift argument to count() in the formula feature, shifting back by one day seven times, adding together and then dividing by seven.
In the request to Elasticsearch, each daily time shift is translated directly to specific dates by Kibana Lense.
I would like to generalise this to make it more adaptable and easier to manage. For example to extend the reference time period to several weeks or months or to implement very specific comparisons like the same weekday of the calendar week over the previous two years.
Can I implement this using one of Elasticsearch or Kibanas features or would it have to be calculated before ingesting to Elasticsearch?
I have looked at the various visualisation tools in Kibana (Lense, TSVB, Timelion, apart from the Custom visualisation) and at runtime fields, but could not find a solution.
Is it possible to create lagged data with runtime fields? So far, I have only seen cases where information from a specific document was processed to create a field value for that document. I would like to supplement a document with information from a set of other documents. Can I append a document with information from another document based on a shifted timestamp, e.g. using the retrieve fields from related indices feature?
As another example, imagine having the FlightDelayMin (flight delay in minutes) aggregated to hourly averages and comparing this to the same hour the year before. The next screenshot shows the idea (without the aggregation) with the additional fields FlightDelayYearBefore and WeeklyAveFlightDelayYearBefore where the values would be taken from a document with a timestamp of Mar 29 2022 @ 23:XX:XX.000 and documents matching a time frame from the week from the previous year.
