# Substract value of one attribute from the previous day value in ELasticsearch for Kibana Visualization

**URL:** <https://discuss.elastic.co/t/substract-value-of-one-attribute-from-the-previous-day-value-in-elasticsearch-for-kibana-visualization/337184>\
**Category:** Kibana\
**Created:** [June 29, 2023, 11:27am UTC](https://discuss.elastic.co/t/substract-value-of-one-attribute-from-the-previous-day-value-in-elasticsearch-for-kibana-visualization/337184 "2023-06-29T11:27:44Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![gauravpks](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gauravpks/32/111389_2.png) [@gauravpks](https://discuss.elastic.co/u/gauravpks)\
**Post date:** [June 29, 2023, 11:27am UTC](https://discuss.elastic.co/t/substract-value-of-one-attribute-from-the-previous-day-value-in-elasticsearch-for-kibana-visualization/337184/1 "2023-06-29T11:27:44Z")

</div>

My elastic index has 3 attributes: - accountNumber, timestamp and score. I want to calculate the difference in score from today to the previous day (or the last value) for each account and build visualizations for the difference. How can I calculate the difference in score for each account and save in a derived variable?  
I need help with scripted / runtime variables. New to elasticsearch

## tried this - but getting error with new BoolQueryBuilder()

// Retrieve today's score  
def todayScore = doc['score'].value;

// Search for the last score for the current accountNumber  
def lastScore = 0; // Default value if no score found  
def lastQuery = new BoolQueryBuilder().must(  
QueryBuilders.termQuery('accountNumber', doc['accountNumber'].value)  
).mustNot(  
QueryBuilders.termQuery('\_id', doc['\_id'].value) // Exclude the current document  
);  
def lastResult = client().prepareSearch('your\_index\_name')  
.setQuery(lastQuery).addSort(SortBuilders.fieldSort('timestamp').order(SortOrder.DESC)).setSize(1).get();  
if (lastResult.getHits().getTotalHits() \> 0) {  
def lastDoc = lastResult.getHits().getAt(0);  
lastScore = lastDoc['score'].value;  
}

// Calculate the score difference  
def scoreDifference = todayScore - lastScore;  
return scoreDifference;

---

<div class="post-metadata">

**Author:** ![jsanz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jsanz/32/53734_2.png) [@jsanz](https://discuss.elastic.co/u/jsanz)\
**Post date:** [July 18, 2023, 3:17pm UTC](https://discuss.elastic.co/t/substract-value-of-one-attribute-from-the-previous-day-value-in-elasticsearch-for-kibana-visualization/337184/2 "2023-07-18T15:17:25Z")

</div>

Since you posted in the Kibana forum I'll provide a Kibana answer 🙂

In lens you have a `Differences` function that is defined as:

> The change between the values in subsequent intervals.

I used it in the following clip to show a line chart with the average value of a field and, on a second data layer the difference of those values.

![Peek 2023-07-18 17-07](https://us1.discourse-cdn.com/elastic/original/3X/a/d/adae0cf1a27e7db213331b258c03b1fabd10db0b.gif)

Maybe you can post this again in the Elasticsearch forum if you'd prefer a solution in the backend.

---

<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:** [August 15, 2023, 3:18pm UTC](https://discuss.elastic.co/t/substract-value-of-one-attribute-from-the-previous-day-value-in-elasticsearch-for-kibana-visualization/337184/3 "2023-08-15T15:18:05Z")

</div>

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