When we talk about the table calculation in the tableau, several average concepts are not that easy to distinguish.
- Moving average
- Running average
- Window average
In the tableau, calculating the moving averages are simple. Just add a table calculation.
The calculation field of this table calculation is created by this ‘window_AVG’, window average.
Because calculating this value is so easy in tableau. I barely think about how I need to calculate them manually. Until one day, I need to calculate the Running averages in the Alteryx, which made me need think carefully about what these averages are.
Here is the case:
The dataset looks like this:
The Alteryx does not have the tools to calculate the moving average or running average. So, I need to google how to calculate it manually.
I have a question back then, for column Num1, how do I calculate the 1st and 2nd rows of running average of 3 months? Then I know that,
the 1st row of moving average = (218 x 3)/3
the 2nd row of moving average = (218 x 2 + 100)/3
If I know how to calculate it, the rest of the thing is to build up the workflow. Apart from calculating, this table is perfect for practising the ‘Cross Tab’ or ‘Transpose’ tool.
The whole workflow would be like this:
- Before the Transpose, the dataset needs to be added to the record ID. In the case of the different structures when Cross tab it back.
- The Transpose tool and sorting tool are used for pre-processing. The data would be like this after using these two tools.
3. Then the multi-row formula tool is for calculating the running average.
4. Then Cross Tab them back and join all the tables together
That’s the Alteryx way to deal with the Running average, which is much more complicated than Tableau. But it is still a good practice for the basic tools.