Cumulative sum of a value in an array

Hi Everyone,

I need help on an expression solution. The “sum” should be the sum of the value(s) of “key2” from previous records till the current record within the array.

I’m trying to make it using the “reduce” function.

Input:

[
  {
    "key1": "value1",
    "key2": 10
  },
  {
    "key1": "value2",
    "key2": 20
  },
  {
    "key1": "value3",
    "key2": 30
  }
]

Expected Output:

[
  {
    "key1": "value1",
    "key2": 10,
    "sum": 10
  },
  {
    "key1": "value2",
    "key2": 20,
    "sum": 30
  },
  {
    "key1": "value3",
    "key2": 30,
    "sum": 60
  }
]

Hey @spinaka,

If the records are in an array, than use the following expression:

$array.reduce((acc,curr)=> acc+curr.key2, 0)

Or if you are able, you can split the array and use the Aggregate snap with SUM function selected.

Regards,
Bojan

Blockquote

Hi @bojanvelevski, Thanks for the response.
This Aggregation just gives the sum of all the values that is 60., but we need the cumulative sum in every record as shown in the example output.

sum = sum in the previous record + key2 in the current record.

Here’s a corrected version of the expression:

$array.map((x,index)=> index==0 ? x.extend({sum:x.key2}) : x.extend({sum:$array.slice(0,index+1).reduce((acc,curr)=> acc+curr.key2,0)}))

Let me know if this works for you, and I will explain in details on how the expression works.

image

3 Likes

This is perfect @bojanvelevski
I was trying with index, map and your expression is better than mine. :slight_smile:

Would you mind explaining the reduce function used here?

I’m glad I could help,

Because we need to add the ‘sum’ field in every object within the array, the expression starts with the .map() function. While iterating, I’m adding the index argument so we could use it for 2 operations, one is to check if the current element we are mapping is the first one in the array index==0 ? x.extend({sum:x.key2}), if so, than extend that object with the value of key2, because the first object will always have the sum value same as the key2 value.

And if not, than we include the index in the second operation, which is slice the whole array from the start (0) up to the index + 1. For each element we have a new sliced version of the array where we can easily use the .reduce() function to calculate the cumulative value of key2.

Example 1:

For index 1, the array will be sliced from 0 to 2, and the reduce function on key2 will return 30.

Example 2:

For index 2, the array will be sliced from 0 to 3, and the result 60

After we have the cumulative value, we extend the current object with the sum field and the value of the calculation. I hope I’m making myself clear enough,

Regards,
Bojan

Absolutely, Bojan. The slice was the one I missed. I appreciate your help on this.

1 Like