Rolling averages smooth fluctuations in time-series data, making underlying patterns easier to see. This article explains:
- What rolling averages are and when to use them
- How to apply a rolling average to a table
- How rolling averages affect statistical tests
- How to choose the advanced settings
- Differences between Displayr and Q
What rolling averages are and when to use them
Consider the chart below. Many of the series move substantially from period to period. This makes it difficult to spot trends. Rolling averages are a solution to this problem.
Rolling averages replace the calculated statistics (e.g., Column %, Row %, Average) with an average computed across two or more time periods. For example, if in January awareness was 10%, in February it was 20%, and in March it was 12%, the 3-month rolling average for March is (20% + 10% + 12%) / 3 = 14%.
The data from the chart above is shown below as a three-month moving average. Note that:
- There is much less variation in each of the colored series, making it easier to read. To use the jargon, it has been smoothed.
- The data from January and February is not shown. This is because with a three-month rolling average, we replace each value with the average of three months. For the first two periods, there isn't three months of data, so the value cannot be computed. Mechanisms for addressing this are discussed in How to choose the advanced settings.
How to apply a rolling average to a table
To apply a rolling average to a table:
- Create a table with a Date/Time variable set in the columns.
- In Data Sources, select the variable set that is used in the rows of the table and press the Rolling Averages button.
- Specify the number of periods in the Periods control.
- (Optionally) Set Period weighting to Weighted sample size if you want each period to be weighted proportional to its (weighted) sample size. Otherwise, each period's data is given an equal weight (i.e., the average is the unweighted average of the periods).
- (Optionally) If Period weighting is set to Equal, you can modify the setting for Minimum period sample size. This setting specifies the smallest sample size to use when calculating a rolling average. When Period weighting is set to Equal, missing values will appear if any of the time periods have a smaller sample size.
Smoothing is applied to any statistic that is used to describe a pattern in data, such as averages, percentages, and coefficients. It is not applied to sample size and population statistics (e.g., Count, Column Sample Size), nor to statistics used in significance testing (e.g., standard errors, p-values, or the arrows used to denote significance testing).
How rolling averages affect statistical tests
Applying a rolling average changes the values displayed in the table, but it does not change Displayr’s significance tests. The tests continue to compare the original, unsmoothed periods.
This is deliberate. Neighboring rolling averages usually contain many of the same observations. They therefore cannot be treated as independent samples, as assumed by most standard tests.
It is possible to construct a test that accounts for the overlap. However, such a test answers whether the two rolling windows differ, rather than whether the underlying periods differ. The result can be unintuitive. For example, with three-period averages, the difference between January–March and February–April depends only on January and April: February and March appear in both averages and cancel out.
Displayr therefore keeps the statistical tests tied to the original periods, where their interpretation is clearer.
If it is necessary to apply such tests, this can be done by creating new variables that contain the overlapping time periods, and then applying such tests to the new variables.
Where a cell on a table is shown as being significant, but seems to contain no data, it is probably because the unsmoothed data is significant, but the smoothed data has not been displayed due to other options (such as End set as Missing).
How to choose the advanced settings
A trailing rolling average is appropriate when a contract, reporting standard, or business rule specifically requires one. It can also provide a simple summary based only on information available at the time.
However, when the objective is to remove noise and reveal the underlying trend, a simple trailing rolling average has three important limitations:
It looks backward.
It assumes that the trend locally is flat.
Periods enter and leave abruptly.
Each of these limitations can be addressed using more advanced settings. However, a consequence of these more advanced settings is when new data is received, old results may change. For example, if a data series finished in November and shows a rolled average of NPS of 23 for November, and another month of data is received, the value for November may change and no longer be 23. Such a revision is often appropriate; the purpose of the rolling averages is to pool information across time. However, this problem does not occur when using trailing rolling averages.
It looks backward
Suppose the March value is calculated by averaging January, February, and March. Although the result is labelled March, the three included periods are centered on February. The data therefore tends to describe February better than March.
Setting Type to Centered instead calculates the March result using February, March, and April. This removes the backward bias, but it also means that the result cannot be calculated until April’s data are available. It is therefore suitable for reviewing historical trends, but not for real-time reporting. However, this disadvantage can be overcome via the End setting, which is available for Type of Centered and Tricubic. This setting has the following options:
Partial: This is the default. It uses the available data. For example, if using a three-period rolling average, but only two periods are available, the moving average is computed with the data from the two periods). This introduces a bias. For example, if there is a five period moving average, for monthly data for March, and the data only starts at March, then using Partial will assign a value to March that is the average of March, April, and May. This bias only exists when setting Degree at 0 (discussed later).
Missing: Returns a NaN if there is incomplete data to calculate. For example, with a three-period centered rolling average, the first and last periods are NaN, and with a five-period rolling average, the first two and last two periods are always shown NaN.
Full (shifted): Ensures that the specified number of Periods is always used. For example, if a data series starts in January, and has a three-period rolling average, then the value for January is calculated using data for January, February, and March. Where Degree is 0, this means the value for January will be identical to March. When Degree is set to higher values, this problem is avoided.
It assumes that the trend is locally flat
An ordinary rolling average calculates one constant level across all the periods used in the calculation
That assumption can lead to the rolling average misrepresenting the underlying trend:
- A trailing average tends to sit below a rising trend and above a falling trend.
- A centered average avoids this delay for a straight trend, but still tends to overly flatten peaks and fill in troughs.
A mechanism for solving this is to use regression and interpolate the values, rather than calculate an average. For many problems, a quadratic regression effectively avoids the problem of assuming the trend is locally flat. This can be specified by setting Degree of 2. Setting Degree to 1 results in a linear regression, and 0 calculates the average (the intuition for this is that degree of 0 is a simple regression with only an intercept, which is the same as the average).
Although setting Degree to 2 is generally much better than a rolling average, there are better methods, such as thin-plated splines. However, these are considerably more complex to use.
Periods enter and leave abruptly
In a conventional rolling average, every included period receives equal weight. As the window moves, a new period suddenly receives full weight while an old period suddenly receives no weight.
Setting Type to Tricubic makes this transition smoother. Periods closest to the target receive the greatest weight, while more distant periods gradually fade towards zero. This is the weighting convention used in locally weighted regression methods such as LOWESS and LOESS.
For a three-period window, the relative tricube weights are:
0.67, 1.00, 0.67
After these are scaled to add to 100%, the center period receives approximately 43% of the total weight, and each adjacent period receives approximately 29%.
The charts below show the relative tricube weights for 11-, 7-, and 3-week windows.
This weighting is multiplied by the weighting set when Type is Tricubic. Weight periods equally
Differences between Displayr and Q
Q permits a different rolling average to be specified for every row of a table. And, it uses different language to describe the rolling averages. Nevertheless, the two apps are compatible. If you upload a QPack containing different rolling averages within the same table, it will still work in Displayr. But, you cannot edit the settings (other than clicking Restore and then configuring them all at the same time in Displayr). And, if you export from Displayr, it can be opened in Q.