Alteryx Community's Challenge #3 asks to calculate 3 and 6 month running averages. It is not entirely straightforward or intuitive how to do that, hence the blog post.

Let's begin by looking into the dataset.

We are asked to create a 3 and 6 month running averages for the values contained in the following columns: c.LK98, p.LK98, c.1K, p.1k, c.NLP3, p.NLP3 by RM Category.

You want to transform the table into the following format.

To do so, you may want to use a Select tool to rename the c.LK98, p.LK98, c.1K, p.1k, c.NLP3, p.NLP3 into Measure 1, Measure 2, etc.; a Record ID tool to keep the record of your initial rows; a Transpose tool to pivot all your Measure fields; another Select tool to rename a column after using the Transpose tool; an Imputation tool to change null values into zeros; and a Sort tool to sort your data based on Measure field (ascending) and Record ID (ascending). If any step is unclear, have a look at the solution file here.

Finally, when we have the required table, we will calculate the running averages using Multi-Row Formula tool.

The configuration of the first Multi-Row Formula tool for the 3 month running average is as follows:

Because there is no running average formula or tool in Alteryx, we need an expression to tell Alteryx to calculate an average based on the current row and two rows before:

([Row-2:Value]+[Row-1:Value]+[Value])/3

The 6 month running average then requires another Multi-Row Formula tool with a configuration as follows:

Because we are taking six months into account now, the expression is slightly longer than before:

([Row-5:Value]+[Row-4:Value]+[Row-3:Value]+[Row-2:Value]+[Row-1:Value]+[Value])/6

Both of these steps will get you to your running averages, but if you want to finish the challenge, your workflow should look something like this:

Do check the solution file again if you are not sure why one or another tool is used after the two Multi-Row Formula tools.