Year-Over-Year Trends
Jozef Soročin
Updated 07/13/2025
In a variety of circumstances you may want to calculate trends — how a metric is changing with respect to some baseline value. Let's look at a typical insurance use case.
- I'm an insurance portfolio manager and want to know by how much the total contractual coverage (also called "sum insured") changed in 2020 compared to 2019 — both in $$$ and in percent.
- On top of that, I want to explore these YoY trends in two separate bands: where the coverage is < $1M and where it's ≥ $1M.
At the end of the day, I'm requesting a table looking like this:
| Coverage Band | Coverage change in $ | Coverage Change in % |
|---|---|---|
| < $1M | -$727,050.00 | -62.41% |
| ≥ $1M | $413,000.00 | 7.84% |
Let's assume a highly simplified insurance contract of the form:
{
"created_at": "2019/03/12 07:21:17",
"coverage": 570000
}PUT portfolio
{
"mappings": {
"properties": {
"created_at": {
"type": "date",
"format": "yyyy/MM/dd HH:mm:ss"
},
"coverage": {
"type": "float"
}
}
}
}POST _bulk
{"index":{"_index":"portfolio"}}
{"created_at":"2019/03/12 07:21:17","coverage":570000}
{"index":{"_index":"portfolio"}}
{"created_at":"2019/06/21 13:43:58","coverage":324000}
{"index":{"_index":"portfolio"}}
{"created_at":"2019/09/07 21:32:38","coverage":1250000}
{"index":{"_index":"portfolio"}}
{"created_at":"2019/10/22 05:52:15","coverage":3600000}
{"index":{"_index":"portfolio"}}
{"created_at":"2019/12/28 14:24:43","coverage":998000}
POST _bulk
{"index":{"_index":"portfolio"}}
{"created_at":"2020/05/01 12:56:17","coverage":860000}
{"index":{"_index":"portfolio"}}
{"created_at":"2020/05/01 23:52:17","coverage":275000}
{"index":{"_index":"portfolio"}}
{"created_at":"2020/08/14 13:33:34","coverage":3600000}
{"index":{"_index":"portfolio"}}
{"created_at":"2020/11/06 10:49:26","coverage":29950}
{"index":{"_index":"portfolio"}}
{"created_at":"2020/11/07 17:51:27","coverage":1663000}Next, we'll leverage the facts that:
- row-based calculations can be achieved through
filtersaggregations as outlined here - and that a
bucket_scriptaggregation can execute a script which performs per bucket computations on specified metrics. In our case, the buckets will be results ofsumaggregations on a contract'scoveragefield.
In pseudo-code, we're therefore looking at something along the lines of:
POST portfolio/_search
{
"size": 0,
"aggs": {
"coverage_bands": {
// filter (range) aggregations
"< $1M": "coverage range < 1e6",
"≥ $1M": "coverage range ≥ 1e6"
},
"aggs": {
"coverage_trends": {
// filtered sum aggregations
"baseline": {
"filter": created_at within 2020,
"sum": of the contracts' coverage
},
"comparison": {
"filter": created_at within 2019,
"sum": of the contracts' coverage
},
// as many bucket script aggregations as we like; in our case 2
"yoy_trend_percent": {
"bucket_script": "(baseline - comparison)/baseline) * 100"
},
"yoy_trend_absolute": {
"bucket_script": "baseline - comparison"
}
}
}
}
}Translating that into a full DSL query would mean: