Calculate a moving average

A moving average is a function (MovingAvg in Sigma) 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.

MovingAvg is a type of window function. Window functions act on the data within the groupings they are placed in, and are affected by the order of the rows.

To set up a moving average, first define the 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.

The high-level steps for calculating a moving average are:

  1. Create the groupings.
  2. Create a unique sort order.
  3. Enter the moving average formula.

This example describes a four week moving average of weekly sales data broken down by store and department, using the PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA sample data.

  1. In a workbook, select a new table, PLUGS_ELECTRONICS_HANDS_ON_LAB_DATA.
  2. Move the [Store Name], [Date], and [Product Family] columns to the left, for easier navigation.
  3. Group by store name. In the left panel, drag [Store Name] to the GROUPINGS section.
  4. Group by product family. In the left panel, drag [Product Family] to the GROUPINGS section, to make a second grouping. Do not put it the same group as [Store Name].
  5. Group by date. In the left panel, drag [Date] to the GROUPINGS section to make its own grouping. Do not put it in with the other groupings. Sigma automatically groups the column and names it [Day of Date].
    The workbook looks like this:
    movavg-initial-groupings.png
  6. Use the menu in the [Day of Date] grouping to change how the date is truncated. Change it from day to week. Sigma automatically updates the column title to [Week of Date].
    movavg-trunc-week.png
  7. Create a [Revenue] column.
    • From the Date column menu, select Add new column. Sigma creates a column called [Calc].
    • Enter the formula:
      [Quantity] * [Price]
    • From the [Calc] column menu, rename the column [Revenue].
  8. From the [Week of Date] grouping's CALCULATIONS menu, select [Revenue]. Sigma automatically calculates the sum and changes the name to [Sum of Revenue].
  9. If it's not already, sort the [Week of Date] column ascending. The workbook looks like this:
    movavg-sumofrevenue.png
    Tip: 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.
    Next, calculate the Moving Average.
  10. From the [Sum of Revenue] column menu, select Add new column. Sigma creates a column called [Calc].
  11. Enter the formula:
    MovingAvg([Sum of Revenue], 3)
  12. The formula takes the column [Sum of Revenue], which represents the weekly sales, and averages the current row with the three above it. This computes a four week moving average. The result is a [Product Family] level four week moving average for every product family in every store.
    movavg-final.png
    Tip: If you want to sort the weekly data descending, you could use the following formula to average the current row with the three rows below it. 
    MovingAvg([Sum of Revenue], 0, 3)
    Tip: As a window function, MovingAvg is confined by the groupings, and will only use the rows in its own group in calculating the averages.

Related resources

 

Was this page helpful?
Yes No