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

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)
QueryBuilders.termQuery('_id', doc['_id'].value) // Exclude the current document
def lastResult = client().prepareSearch('your_index_name')
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;

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

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

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

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