Use Moving Average

Moving Average calculates average values for a specified window and plots the values on a time series graph. A moving average creates a smoothing effect and reduces noise from daily fluctuations. Moving Average can also be used to impute missing data with estimated values.

Examples

The following are example scenarios using Moving Average:

• A stock market analyst is analyzing the value of various stocks. The analyst calculates a moving average to track the trends in the stock prices and determine which stocks are growing in value and which stocks are losing value.
• An epidemiologist is studying an outbreak of an infectious disease, but her dataset is missing values for several dates. A central moving average is used to calculate estimated values for the missing dates.

Run Moving Average

Moving Average can only be run using a time series graph with a numeric field on the y-axis.

Complete the following steps to calculate a moving average:

1. Create a time series chart using one of the following data combinations:
   • One date/time field and one or more number or rate/ratio fields
   • One or more date/time fields and one number or rate/ratio field
2. Click the time series card to activate it if necessary.

   A card is active when the toolbar and Action button appear.

3. Click the Action button and choose Moving Average.
4. For Choose number field or Choose date/time field, select the number or date/time field to use to calculate the moving average.

   This parameter is only available if the time series graph was created with multiple number fields or multiple date/time fields.

5. For Moving average period, provide the number of days to use in the moving average calculation and adjust the slider if necessary.

   See the Usage notes section below for more information.

6. Expand Additional options and change the Daily aggregation parameter value if necessary.
7. Click Run.

Usage notes

The Choose a number field parameter is available on time series charts created using two or more numbers on the y-axis. The Choose date/time field parameter is available when two or more date/time fields are used on the time series. These parameters specify the field to use when calculating moving average.

The Moving average period parameter specifies how many days are included in the moving average calculation. For example, to calculate a one-week moving average, use a period of seven days. The period must be an odd number. The default period is three days.

The slider in the Moving average period parameter specifies which days are used in the calculation. The most common uses of moving average place the calculated value (labeled as day 0 on the slider) as either the endpoint (trailing moving average) or the midpoint (central moving average). By default, Moving Average calculates a central moving average, meaning there is an equal number of data points before and after the value being calculated.

You can move the slider to change the days used in the calculation. Moving the slider to the negative end changes the calculation to a trailing moving average. For example, using the default period of 3 creates a trailing moving average where the date being calculated and the preceding two days are used in the calculation.

You can also use the slider to create a moving average calculation with a custom number of days before and after the day being calculated, rather than using a central or trailing moving average.

Moving average is calculated using a single data point for each day. The Daily aggregation parameter specifies how multiple values from a single day are aggregated into a single data point. The aggregation options include average, minimum, and maximum. The default aggregation is average.

The moving average line is added to the time series graph. A new n-day moving average (where n is the value of the Moving average period parameter) field is added to the input dataset. You can run Moving Average multiple times on the same dataset. Each result is added to the time series graph and appended to the input dataset.

Limitations

Moving Average can only be run from a time series graph created using at least one number field.

The Moving average period parameter value must be an odd number between 3 and 999.

How Moving Average works

Moving Average uses a day-based simple moving average (SMA) calculation. The Moving average period parameter and the corresponding slider are used to determine which days are used in the calculation.

The moving average calculation applies the moving average period to dates in the dataset, not calendar days. If a date is missing from the dataset, the next closest date in the dataset will be used, rather than using the consecutive calendar date. Datasets with large ranges of missing dates may result in inaccurate calculations near the beginning and end of the missing dates.

Dates containing null or missing data are included in the period but subtracted from the value of n (see the equation below). If you do not want to include null or missing data, you can apply a filter to the dataset before running Moving Average. For example, you want to calculate moving average based on business days and exclude null values for weekends. You can apply a filter based on the Day of week subfield for the input date/time field to remove values for Saturday and Sunday.

The data for each day is aggregated using the statistic in the Daily aggregation parameter and is applied to the following formula:

MA₀ = (d₁+...+d_n-1+d_n)/(n-x)

where

• MA₀ = The moving average for the current date. This date can correspond to any position in the equation (d₁ to d_n).
• d = The aggregated daily value.
• n = The number of daily values in the moving average calculation (the moving average period).
• x = The number of aggregated daily values within the moving average range that are null. This value can range from 0 to n-1 in a valid calculation. If x = n, the moving average will not be calculated for that day.