Modify Whole Table or Plot - Replace with Rolling Averages

This rule computes a rolling average on a table. There are options for controlling the number of periods in the moving average and whether to roll by the rows or columns.

Technical details

A lagged rolling average is computed. For example, if January has a score of 3, February 4 and March 6, the 3-period rolling average for March is (3 + 4 + 6) / 3 = 4.33333333. More sophisticated rolling averages can be computed using Time Series Analysis (see How to Compute a Moving Average).
Where values cannot be computed due to insufficient data, the value is shown as NaN. For example, if January has a score of 3, February 4 and March 6, the 3-period rolling average for the three months, respectively, are NaN, NaN and 4.33333333.
This rule is only applied to the first Statistic selected in each of Statistics - Cells and whichever is appropriate of Statistics - Below and Statistics - Right.
You may not have a NET in the variable containing the dates.

How to apply this rule

For the first time in a project

Select the table(s)/chart(s) that you wish to apply the rule to.
Start typing the name of the Rule into the Search features and data box in the top right of the Q window.
Click on the Rule when it appears in the QScripts and Rules section of the search results.

OR

Select Automate > Browse Online Library.
Choose this rule from the list.

Additional applications of the rule

Select a table or chart that has the rule and any table(s)/chart(s) that you wish to apply the rule to.
Click on the Rules tab (bottom-left of the table/chart).
Select the rule that you wish to apply.
Click on the Apply drop-down and choose your desired option.
Check New items to have it automatically applied to new items that you create. Use Edit > Project Options > Save as Template to create a new project template that automatically uses this rule.

Removing the rule

Select the table(s)/chart(s) that you wish to remove the rule from.
Press the Rules tab (bottom-right corner).
Press Apply next to the rule you wish to remove and choose the appropriate option.

How to modify the rule

Click on the Rules tab (bottom-left of the table/chart).
Select the rule that you wish to modify.
Click Edit Rule and make the desired changes. Alternatively, you can use the JavaScript below to make your own rule (see Customizing Rules).

JavaScript

rollingAverage = function(x, n_periods) {
    Array.prototype.sum = function () {
        var total = 0;
        var i = this.length; 

        while (i--) {
            total += this[i];
        }

        return total;
    }

    var n_columns = x[0].length;
    var n_rows = x.length;
    var result = new Array(n_rows);
    for (var c = 0; c < n_columns; c++) {
        var sum = 0;
        for (var r = 0; r < n_rows; r++) {
            if (c == 0)
                result[r] = new Array(n_columns);

            if (r >= n_periods - 1) {
                var array_subset = x.map(function (arr) { return arr[c]; } ).slice(r - n_periods + 1, r + 1);
                result[r][c] = array_subset.sum() / n_periods; 
            } else {
                result[r][c] = NaN;
            }
        }

    }
    return(result); 
}





includeWeb("Table JavaScript Utility Functions");

// Create the form 
form.setHeading('Rolling (moving) average');
var number_label = form.newLabel('Number of periods to roll:'); 
var numeric_up_down = form.newNumericUpDown('periods'); 
numeric_up_down.lineBreakAfter = true;
numeric_up_down.setDefault(3);
numeric_up_down.setIncrement(1); 
numeric_up_down.setMinimum(1); 
var within_rows_cb = form.newCheckBox('check',"Dates are in the rows");
within_rows_cb.setDefault(false);
form.setInputControls([number_label, numeric_up_down, within_rows_cb]);
var within_rows = within_rows_cb.getValue()
var n_periods = numeric_up_down.getValue()
form.setSummary(n_periods + " period rolling average (within " + (within_rows ? "columns" : "rows") + ")");


 
var labs = within_rows ? table.rowLabels : table.columnLabels;

if (labs == null) {
    form.ruleNotApplicable("there are no " + (within_rows ? "rows" : "columns") + " to average over. Toggle the 'Dates are in the rows' setting above");
}

if (labs.indexOf("Total") != -1 || labs.indexOf("NET") != -1)
    form.ruleNotApplicable("this rule can only be applied where the question containing dates contants no NET or Totals.");

if (!within_rows && n_periods > table.numberColumns)
    form.ruleNotApplicable("there are fewer columns than periods to average over");
if (within_rows && n_periods > table.numberRows)
    form.ruleNotApplicable("there are fewer rows than periods to average over");
if (table.statistics[0] == "Text")
    form.ruleNotApplicable("this rule can only be applied to a numeric table. Try using Text Analysis - Automatic Categorization to create categorical variables to tabulate");

// Peforming rolling average on main table.
var values = table.get(table.statistics[0]);
if (!within_rows)
    values = Q.transpose(values);
values = rollingAverage(values, n_periods);
if (!within_rows)
    values = Q.transpose(values);
table.set(table.statistics[0], values);

// Peforming rolling average on marginal table table.
if (within_rows ? (rightTableExists() && right_table.statistics.length > 0) : (belowTableExists() && below_table.statistics.length > 0)) { 
    var marginal_values = within_rows ? right_table.get(right_table.statistics[0]) : below_table.get(below_table.statistics[0]);
    var needs_transpose = marginal_values.length == 1;
      
    if (needs_transpose) {
        marginal_values = Q.transpose(rollingAverage(Q.transpose(marginal_values), n_periods));
    } else {
        marginal_values = rollingAverage(marginal_values, n_periods);
    }
    if (within_rows)
        right_table.set(right_table.statistics[0], marginal_values);
    else
        below_table.set(below_table.statistics[0], marginal_values);
}

Modify Whole Table or Plot - Replace with Rolling Averages

Technical details

How to apply this rule

For the first time in a project

Additional applications of the rule

Removing the rule

How to modify the rule

JavaScript

See also

Navigation menu

Search