Calculating a Moving Average with Alteryx

To calculate a moving average in Alteryx, follow these steps:

1. Get your data into at least two columns: a date field and the sum of something by date. In this case I have Year and Profit for Dar es Salaam from the Global Superstore data set.
2. Sort your rows so that they are Ascending by the date field.

3. Drag a Multi-Row Formula to the canvas.
4. In the configuration of your Multi-Row Formula, set the Num Rows to the number of date parts you want the moving average to calculate over. For example, if you want a two-year moving average and your date field is Year, then set Num Rows to 2.
5. Set Values for Rows that don’t Exist to ‘Set to Values of Closest Valid Row’.
6. Now you need to write your formula. Under Variables, you’ll see drop-downs for Row-2, Row-1, etc. Write the AVERAGE function, then open each of the drop-downs that you need and select the field you want to calculate the moving average of. For example, if you want to calculate the moving average of Profit over the past two years, double-click on Row-1 SUM(Profit), then write a comma, then double-click on Active Row SUM(Profit), then close the brackets.

7. Run the workflow, and Alteryx will calculate your moving average.