Calculating a Running Total or Cumulative Sum | StatisticsCalculator

Introduction

Given a series of numbers, add a new column that shows the running total for each record. For example, we may want a running total of the attribute Net as shown here:

NetRunningTotal

5 | 5

2 | 7

4 | 11

1 | 12

For this article, please assume a running total and a cumulative sum are the same thing.

Step-by-step Instructions

1. Add CSV

Open FME Workbench and add a CSV reader to a blank canvas. Browse to the BalanceSheet.csv file, which can be downloaded from the Files section of this article. 

2. Create Net Attribute

Add an AttributeCreator to the canvas, and connect it to the CSV reader feature type. In the parameters, create a New Attribute called Net. Then, for the Attribute Value, click the drop-down and select Open Arithmetic Editor. 

In the Arithmetic Editor, enter the following equation: @Value(Income)-@Value(Expenditures)

3. Add a StatisticsCalculator

Add a StatisticsCalculator to the canvas and connect it to the AttributeCreator. For Statistics to Calculate, select the Net attribute, then enable Sum. This will add up all of the numbers in Net. Next, enable Advanced - Output Cumulative Results, set the Attribute to Net, and enable Sum. 

4. Rename the Attribute

Add an AttributeRenamer to the canvas and connect it to the Cumulative output port on the StatisticsCalculator. If you do not see this output port, open the parameters and ensure that Advanced - Output Cumulative Results is enabled.

In the AttributeRenamer parameters, select Net.sum as the Input Attribute, then set the Output Attribute to RunningTotal.

5. Run the Workspace

Connect an Inspector to the AttributeRenamer, then save and run the workspace. View the results in Visual Preview. Note that the RunningTotal adds all of the Net values as each row progresses.