How to Compute a Time Series Moving Average

Introduction

This article describes how to go from a table showing date periods in the columns:

To a table which calculates and displays a moving average:

Method

Basic method

A basic moving (rolling) average can be placed on a table by applying the rule Automate > Browse Online Library > Modify Whole Table or Plot - Replace with Rolling Averages. 

General method

1. Ensure that your date data is set up as a Date question.
2. Ensure that your date question is in the appropriate unit (e.g., if you want to show a 5 month moving average, ensure that the date question shows months). 
3. Create a table with the Date question in the brown drop-down box. 
4. Right-click on the row labels that you wish to smooth (not on the date categories) and select Time Series Analysis, which opens the following dialog box:
   
   
   
   
5. Set the Moving Average as Uniform (centered) or Lagged.
6. Set the Bandwidth to the number of months that you wish to combine (e.g., three) and press OK.

Note that if you wish to show both the moving average and the un-smoothed data together you can copy a row in a table by selecting the blue row header for the row you want to copy, then hold down the Ctrl button and drag the row to were you want to create the copy. Release the mouse button to add the new row. 

Creating Variables

You can create new variables for rolling time periods by right-clicking a Date question in the Variables and Questions tab, and selecting Insert Ready-Made Formulas > Date -> Moving Average.  The following dialogue box will appear where you can set the parameters for the moving average average variable: 

• Input dates The date question you selected as the basis for the moving average.
• Start/End The range of dates found in the date question.
• Unit length The number of time units (see Unit) to include in each data point.
• Unit The units for Unit length. For example, if Unit length is 2 and Unit is Month then each data point will cover two months.
• Moving average The number of data points to average over. For example, if you select 4 here and your data points cover two months each, then each point in the moving average will cover eight months.
• Labels How to label the points in the moving average. Choose End Points to use the full date range. e.g. Apr 08 - Jun 08. Choose Midpoint to use the date midway through. e.g. 15 May 08. Use Last Point to use the final date in the range. e.g. Jun 08.
• Start date/End date The date range over which to construct the moving average. If you expect your raw data file to be updated with new data then be sure to include extra dates to avoid having to recreate the moving average later.

Next

How to Show a Moving (Rolled) Average on a Line Chart Visualization

How to Create a Time Series Graph