Forum Discussion

spinaka's avatar
spinaka
New Contributor III
4 years ago
Solved

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
  }
]
expression_language
  • bojanvelevski's avatar
    bojanvelevski
    4 years ago

    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.

6 Replies

  • bojanvelevski's avatar
    bojanvelevski
    Valued Contributor
    4 years ago

    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

    • spinaka's avatar
      spinaka
      New Contributor III
      4 years ago

      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.

      • bojanvelevski's avatar
        bojanvelevski
        Valued Contributor
        4 years ago

        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.

  • bojanvelevski's avatar
    bojanvelevski
    Valued Contributor
    4 years ago

    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

    • spinaka's avatar
      spinaka
      New Contributor III
      4 years ago

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