In this blog I'll be demonstrating how to make a basic running total column in Alteryx. A couple of use cases for this could be for example:
- A running total of sales over time for a business
- A running total of goals a footballer has scored across a season
- A running total for the number of times a product has been sold
In this example we'll be using a small example data set I've created with 10 transactions ordered by the transaction ID:
You will need 2 tools for this process.
- The Select Tool
2. The Multi-Row Formula tool
To create our running total column, the first step would be to ensure the column we will be using in the formula is a numeric format using the Select tool.
In this case the data format is already correctly set as a double meaning we can proceed to the next step.
Now we want to bring in our Multi-Row Formula tool and connect it to the flow. When connected the tool should open a settings pane with 3 different sections that I will go through now:
The first section is where we create our new field. Make sure "Create New Field" is selected and name it "Running Total". Ensure the data type is set to a double and not an integer otherwise the resulting outputs will be rounded to 0 decimal places. In this case there is no need adjust any of the other settings in this pane. We leave the "Values for Rows that don't exist" to "0 or empty" meaning that for the first row in the calculation, the tool will assume the previous (non-existent) row had a value of 0.
In the second section we want to find the variable that we will need for our formula expression. Since we want to add values from out Amount column to the value of the previous row in our new Running Total column, we need to go into the "Row-1" section and find "Row-1:Running Total". Double click this variable and it should appear in the expression box below this section.
All we need to do now in the expression box is add our Amount column to the Row-1 variable to give our tool a multi-row expression to execute through the table.
Once this is added, run the flow and you should get the following output with the running total column:
As the tool moves from row to row, it adds the value in the Amount column on the current row to the value in the Running Total column on the previous row. Since there is no row before row 1 and we instructed the tool to set values for non-existent rows to 0, the running total for the first row is just [Amount] + 0.
I hope this blog was useful!