# Creating customised reference values based on lagged information

**URL:** <https://discuss.elastic.co/t/creating-customised-reference-values-based-on-lagged-information/329036>\
**Category:** Elasticsearch\
**Tags:** ingest-pipeline, runtime-fields\
**Created:** [March 31, 2023, 9:38am UTC](https://discuss.elastic.co/t/creating-customised-reference-values-based-on-lagged-information/329036 "2023-03-31T09:38:29Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![NiklasHBB](https://avatars.discourse-cdn.com/v4/letter/n/b9e5f3/32.png) [@NiklasHBB](https://discuss.elastic.co/u/NiklasHBB)\
**Post date:** [March 31, 2023, 9:38am UTC](https://discuss.elastic.co/t/creating-customised-reference-values-based-on-lagged-information/329036/1 "2023-03-31T09:38:29Z")

</div>

**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).

 ![grafik](https://us1.discourse-cdn.com/elastic/original/3X/0/f/0f076276ed9cc8e2cf4d71767e28bf1a4bf531dd.png)

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.

 ![grafik](https://us1.discourse-cdn.com/elastic/original/3X/7/b/7b8e3c6e228f0ec96a183f9efe328c599d613a31.png)

In the request to Elasticsearch, each daily time shift is translated directly to specific dates by Kibana Lense.

 ![grafik](https://us1.discourse-cdn.com/elastic/original/3X/d/c/dcf949ddb158347f6ea3fa63feb099db195c1b78.png)

**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](https://www.elastic.co/guide/en/elasticsearch/reference/current/runtime-retrieving-fields.html#lookup-runtime-fields) 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.

 ![grafik](https://us1.discourse-cdn.com/elastic/original/3X/8/3/83ce125ace864a5391296e3cea0a570f2ba88d02.png)

---

<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:** [April 28, 2023, 9:38am UTC](https://discuss.elastic.co/t/creating-customised-reference-values-based-on-lagged-information/329036/2 "2023-04-28T09:38:35Z")

</div>

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