Calculating a Moving Average
A Moving Average is a function that is often used to filter out the noise of random price fluctuations and get a better idea of the trend. A moving average takes into account the value of the current row of data, as well as the surrounding rows. For example, if you have weekly sales data, a 4-week moving average would take into account the current week and the previous 3 weeks of data. In Sigma, we can set this up very easily.
Moving Averages are a type of Window Function. Windows functions acts on the data within the groupings they are placed in, and are affected by the order of the rows.
The first step to setting up a Moving Average is setting up your groupings and the sorting for your workbook. For an accurate Moving Average, the workbook must be uniquely sorted. Multicolumn Sorting Criteria and Groupings with no duplicate values are both ways to ensure a unique sort order. Once your data is grouped and sorted, you can accurately apply a Moving Average.
When Calculating a Moving Average:
- Create your Groups
- Create a unique sort order
- Enter your Moving Average formula
Looking again at the example of a four week moving average of weekly sales data, let’s work through the three steps. We want to see a four week moving average broken down by store and department.
Our first step is to group the data by Store Name, then create a second level grouping by Department. We then group by the Date column, and choose to truncate date by week. Then drag the Line Item Total column to the date group to automatically sum the Line Item Totals by week. Last, sort the Week of Date column ascending.
If you can't group by a column to ensure a unique sort order, you can click the gear icon to set up a more complex sort that takes into account multiple columns.
Now that the set up for the window function is done, we can calculate our Moving Average. Create a new column and enter in MovingAvg([Sum Line of Item Total], 3). This tells the function to look at the column [Sum of Line Item Total] (which shows our weekly sales) and average the current row with the three above it. This computes a four week moving average. If we wanted to sort our weekly data Descending, we could use MovingAvg([Sum of Line Item Total], 0, 3) to average the current row with the three rows below it. The function is confined by the groupings, and will only use the rows in its own group in calculating the averages. The result is a department level four week moving average for every department in every store.