# Is it possible to cumulative\_sum over the metric of a moving interval?
**URL:** https://discuss.elastic.co/t/is-it-possible-to-cumulative-sum-over-the-metric-of-a-moving-interval/283519
**Category:** Elasticsearch
**Created:** [September 7, 2021, 9:54am UTC](https://discuss.elastic.co/t/is-it-possible-to-cumulative-sum-over-the-metric-of-a-moving-interval/283519 "2021-09-07T09:54:28Z")
**Posts on this page:** 3
**Page:** 1
### Author:  [@di.lu](https://discuss.elastic.co/u/di.lu)
#### Post date: [September 7, 2021, 9:54am UTC](https://discuss.elastic.co/t/is-it-possible-to-cumulative-sum-over-the-metric-of-a-moving-interval/283519/1 "2021-09-07T09:54:29Z")
Hiii ES community!
I've been stuck on a tricky requirement for quite a while.. So I have a time series data capturing when a user has used what feature as which identity, lie this:
| feature | is\_free\_trial | trace\_id |
| search | true | 000001 |
| register | false | 000001 |
| adv\_search | false | 000001 |
The trace\_id is used to identify an unique visitor interacting with the web app, and is computed from data like IP and User-Agent.
With this data lying in ES, I am hoping to answer " **how many people used free trial features before registering?**". I've created a query that answers the question. But **I couldn't figure out how to get this metric as a cumulative sum over some time period.**.
`date_histogram` + `cumulative_sum` doesn't seem to work because each bucket interval's min date is fixed, whereas a visitor may trial the application a long while ago (can be months) before registration. So I guess what's really needed is a special "interval" setting that has a fixed start date (e.g., since whatever date the application became live) and a moving end date that grows daily/weekly/monthly?
---
### Author:  [@di.lu](https://discuss.elastic.co/u/di.lu)
#### Post date: [September 7, 2021, 10:15am UTC](https://discuss.elastic.co/t/is-it-possible-to-cumulative-sum-over-the-metric-of-a-moving-interval/283519/2 "2021-09-07T10:15:31Z")
PS: the query I used to get the number of users tried free features before registrations is below. Gotta admit that I dont quite like this query especially on how it determines whether the first event's `is_free_trial` in a bucket is `true`...
```auto
{
"aggs": {
"trace": {
"terms": {
"field": "trace.id.keyword",
"size": 1000
},
"aggs": {
"min_ts": {
"min": {
"field": "@timestamp"
}
},
"free_trial": {
"filter": {
"term": {
"is_free_trial": "true"
}
},
"aggs": {
"min_ts": {
"min": {
"field": "@timestamp"
}
}
}
},
"num_registrations": {
"filter": {
"term": {
"feature.keyword": "registration"
}
},
"aggs": {
"count": {
"value_count": {
"field": "event_type.keyword"
}
}
}
},
"is_trial_then_register": {
"bucket_selector": {
"buckets_path": {
"numRegistrationEvents": "num_registrations>count",
"bucketMinTs": "min_ts",
"freeTrialMinTs": "free_trial>min_ts"
},
"script": {
"lang": "painless",
"source": "params.numRegistrationEvents >= 1 && params.freeTrialMinTs == params.bucketMinTs"
}
}
}
}
},
"num_trial_then_register": {
"sum_bucket": {
"buckets_path": "trace>num_registrations>count"
}
}
}
}
```
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [October 5, 2021, 10:16am UTC](https://discuss.elastic.co/t/is-it-possible-to-cumulative-sum-over-the-metric-of-a-moving-interval/283519/3 "2021-10-05T10:16:17Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.