September 14, 2019

Calculate a Moving Average

Learn how to apply a moving average window function to your data using Sigma's spreadsheet analytics interface.

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 act on the group level they are placed in, and are affected by the order of the rows. The first step to setting up a Moving Average is therefore setting up your Groups, and the sorting for your worksheet. For an accurate Moving Average, the worksheet 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:

1. Create your Groups

2. Create a unique sort order

3. 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. We start with a Worksheet with department level weekly sales data from several stores each with several departments. Our first step is to group the data by Store, then create a second level grouping by Department. Within each Department there should only be one entry every week for Weekly Sales, allowing us to Sort Ascending for a unique sort order. To ensure that the sort order is unique, we can use multi column sorting criteria and add a second sort criteria that will be used to break ties.

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([WEEKLY SALES], 3). This tells the function to look at the column [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([WEEKLY SALES], 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.