Creating an Average of Specified Rows

This creates a Q Rule for displaying the average of specified rows.

To use this snippet:

1. Select your table.
2. Select Automate > Custom Rule.
3. Paste in the code from below.
4. Change the labels you want to include in specified_labels.
5. Change the statistic you want to use in statistic.
6. This example uses 'Average %' as the new row label but this can be changed in row 10.
7. Click the 'Play' icon and close.

// Add a new Average row
includeWeb("JavaScript Array Functions");
form.setHeading('Creates an Average % of Specified Rows');
form.setSummary('Creates an Average % of Specified Rows');
// Get the list of statistics on the table.
var statistics = table.statistics;
// What is the last row in this span?
var last_row = table.numberRows - 1;
// Add a new Average row.
table.insertRowAfter(last_row, 'Average %');

// Remember the index of the new Average row.
var average_row = last_row + 1;

// Specify labels and statistic to use
var specified_labels = ["Coca-Cola", "Diet Coke", "Coke Zero"];
var statistic = "%";

// Check for duplicates in row labels / entered labels
var row_labels = table.rowLabels;
var dupe_check1 = arrayHasDuplicateElements(specified_labels); 
var dupe_check2 = arrayHasDuplicateElements(row_labels);
if (dupe_check1)
    form.ruleNotApplicable('the specified row labels have duplicates');
if (dupe_check2)
    form.ruleNotApplicable('the table has duplicate row labels');

// For each row, sum its statistics.

// For each column...
    for (var column = 0; column < table.numberColumns; column++) {
        // For each statistic in the table...
        for (var stat = 0; stat < statistics.length; stat++) {
               var values = table.get(statistic);
               var sum = 0;
               var count = 0;

               for (var row = 0; row < last_row+1; row++) {
                   if (specified_labels.indexOf(row_labels[row]) > -1) {
                       sum += values[row][column];
                       count++;
                   }
               }
            // Store the average in the new Average row.
            values[average_row][column] = sum / count;

            // Store the values of the Average row into the table.
            table.set(statistics[stat], values);
        }      
    }

See also

• Table JavaScript and Plot JavaScript for an explanation of how to run this code.
• Table JavaScript and Plot JavaScript Reference for technical information.
• Table JavaScript and Plot JavaScript Examples Library for other examples.
• JavaScript for information about the JavaScript programming language.
• QScript for tools for automating projects using JavaScript.
• JavaScript Variables for detail on how to create new variables in the Variables and Questions tab using JavaScript.