Spotfire Running Balance (Cumulative Sum)

3
votes

I am trying to create a running balance column in Spotfire that's supposed to look like the picture attached below. In essence,I want to calculate the cumulative total of the "amount" column row by row, and that I want it to start from 0 as the date changes.

I have tried several OVER functions: Sum([AMOUNT]) OVER AllPrevious([Date]) Sum([AMOUNT]) OVER Intersect([CURRENCY],AllPrevious([SETTLEDATE])) Sum([AMOUNT]) OVER Intersect([Calculation Date],AllPrevious([SETTLEDATE]))

Any help is greatly appreciated.

Here is the Picture

2
sumspotfirecumulative-sum

2 Answers

4
votes

You were very close with your first over statement. The problem is, when you use over (AllPrevious([Date])) and you don't have 1 row for each date, then you will skip rows. So, the last row of your data would only sum it over the rows where 6/1/2017 is in the Date column. Instead, we need to apply a RowID to your data set and then sum over it. This will ensure we sum over all previous rows.

Assuming your data set is in the order you want it to be in when you bring it into SpotFire, do the following:

  • Insert a calculated column RowID() and name it RowID
  • Use this calculation: Sum([amount]) over (Intersect([Date],AllPrevious([RowID])))

This will give you the running sum you are looking for.

1
votes

@scsimon- I modified your custom expression slightly to include date as requested in the question.

Modified expression:

Sum([Amt]) over (intersect(Allprevious([rowID]),[Date]))

Final output table:

enter image description here

@LeoL - Hope this answers your question.