Creating an Average of Specified Rows

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

To use this snippet:

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);
}
}