# Substract values before applying aggregation

**URL:** <https://discuss.elastic.co/t/substract-values-before-applying-aggregation/121533>\
**Category:** Elasticsearch\
**Created:** [February 26, 2018, 5:26pm UTC](https://discuss.elastic.co/t/substract-values-before-applying-aggregation/121533 "2018-02-26T17:26:15Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![GuimRH](https://avatars.discourse-cdn.com/v4/letter/g/94ad74/32.png) [@GuimRH](https://discuss.elastic.co/u/GuimRH)\
**Post date:** [February 26, 2018, 5:26pm UTC](https://discuss.elastic.co/t/substract-values-before-applying-aggregation/121533/1 "2018-02-26T17:26:15Z")

</div>

Hello,  
we have an index with two fields (amount others) one is the scheduled time of one job, and the second one is the actual execution time, this second value is used as @timestamp.  
We'd like to have the delay between scheduling and execution for each individual jobs before applying any agregation.

So far I got this:  
`.es(index=myindex-*,metric=avg:@timestamp).subtract(.es(index=myindex-*,metric=avg:@scheduledTime))`  
but I'm not sure it is the solution I want.  
This is first calculating the average value of those fields (wich are timestamps) and then subtracting the results.  
I would like to first substract the values of those fields for each document (so I get the delay of each particular job), and then apply the aggregation I like (max, avg...).  
something like that:  
.es(index=myindex-\*,q="(term:@timestamp)-(term:@scheduledTime)")

Maybe the solution here is to obtain the delay on the source, in my logstash. This solution would also imply we'd had another field (the delay itself) on our index.

Thank you for your help.

---

<div class="post-metadata">

**Author:** ![GuimRH](https://avatars.discourse-cdn.com/v4/letter/g/94ad74/32.png) [@GuimRH](https://discuss.elastic.co/u/GuimRH)\
**Post date:** [February 26, 2018, 5:40pm UTC](https://discuss.elastic.co/t/substract-values-before-applying-aggregation/121533/2 "2018-02-26T17:40:02Z")

</div>

Well,  
I got an alternative, but I don't know if it's the most efficient.  
You can define new fields with scripting: [https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-scripting.html](https://www.elastic.co/guide/en/elasticsearch/reference/current/modules-scripting.html)

with this you set the rule for a new field, in my case:  
`doc['@timestamp'].value-doc['@scheduledTime'].value`

---

<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:** [March 26, 2018, 5:40pm UTC](https://discuss.elastic.co/t/substract-values-before-applying-aggregation/121533/3 "2018-03-26T17:40:23Z")

</div>

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