Table JavaScript Functions for Adding Calculations
Jump to navigation
Jump to search
This page contains functions that can be used to add new rows or columns containing the results of calculations (eg averages).
To make these functions available when writing a QScript or Rule see JavaScript Reference.
addAveragesToSpans
Takes the average for each span and adds a new row or column next to the span to show the average.
// Add a new row or column to each span which shows the average of the statistics for this span
function addAveragesToSpans(by_columns) {
includeWeb('Table JavaScript Utility Functions');
// Choose appropriate heading and summary
let heading_text = 'Add Average Rows to Tables Containing Spans';
let desc_text = 'Adds an average row next to every span, computed as the average of the statistics in the span';
if (by_columns) {
heading_text = heading_text.replace('Rows', 'Columns');
desc_text = desc_text.replace('row', 'column');
}
let summary_text = heading_text.charAt(0) + heading_text.slice(1).toLowerCase();
form.setHeading(heading_text);
form.setSummary(summary_text);
let desc = form.newLabel(desc_text);
desc.lineBreakAfter = true;
let span_choices = by_columns ? ['Left', 'Right'].map(lab => lab + ' of span') : ['Above', 'Below'].map(lab => lab + ' each span');
let span_position_label = form.newLabel('Position: ')
let span_position = form.newComboBox('spanPos', span_choices);
span_position.setDefault(span_choices[1]);
let add_span_label = form.newCheckBox('includeSpanLabels', 'Include span label in new ' + (by_columns ? 'column' : 'row') + ' labels');
add_span_label.setDefault(false);
let add_labels = add_span_label.getValue();
form.setInputControls([desc, span_position_label, span_position, add_span_label]);
// Don't apply the rule for columns when the table can't have any column spans
if (by_columns && table.numberColumns < 2) {
form.ruleNotApplicable(' table cannot have any column spans')
}
let spans = by_columns ? table.columnSpans : table.rowSpans;// Get the list of spans.
// Work out which spans are lowest in the table.
// These are the spans that are tested within.
function getLowestLevelSpans(spans) {
let lowest_spans = [];
let found_indices = [];
spans.forEach(function (span) {
if (span.indices.every(function (x) { return found_indices.indexOf(x) == -1; })) {
found_indices = found_indices.concat(span.indices);
lowest_spans.push(span);
}
});
return lowest_spans;
}
let lowest_spans = getLowestLevelSpans(spans)
lowest_spans = lowest_spans.sort(function (a, b) {
let indices_a = a.indices;
let indices_b = b.indices;
let last_a = indices_a[indices_a.length - 1];
let last_b = indices_b[indices_b.length - 1];
return last_a - last_b;
});
let at_after_position = span_position.getValue().startsWith(by_columns ? 'Right' : 'Below');
for (let i = lowest_spans.length - 1; i >= 0;i--) {// Working backwards through the list of spans...
let span = lowest_spans[i]; // Get the current span.
let span_indices = span.indices;
let span_label = 'Average' + (add_labels ? (' of ' + span.label) : '');
// Determine where to insert the Average Row/Col,
// If the insertion is on the left, the span indices are shifted to accommodate the new row/col.
let insert_index;
if (at_after_position) {
insert_index = span_indices[span_indices.length - 1];
} else {
insert_index = span_indices[0] > 0 ? span_indices[0] - 1 : null;
span_indices = span_indices.map(idx => idx + 1);
}
// Add a row or column for this span
if (by_columns)
insertColumnAfterComplete(insert_index, span_label);
else
insertRowAfterComplete(insert_index, span_label);
// Determine the new position of the average
let average_index = insert_index === null ? 0 : insert_index + 1;
// Compute the averages and add them to the table
table.statistics.forEach(function (stat) {
let values = table.get(stat);
if (by_columns) {
for (let row = 0; row < table.numberRows; row++) {
if (typeof values[0][0] != 'string') {
let sum = 0;
let count = 0;
for (let j = 0; j < span_indices.length; j++) {
var column = span_indices[j];
if (!isNaN(values[row][column])) {
sum += values[row][column];
count++;
}
}
values[row][average_index] = sum / count;
} else if (stat == 'Column Comparisons')
values[row][average_index] = '-'; // Set hyphen to indicate that this cell is not being tested.
}
} else {
for (let column = 0;
column < table.numberColumns;
column++) {
if (typeof values[0][0] != 'string') {
let sum = 0;
let count = 0;
for (let j = 0; j < span_indices.length; j++) {
let row = span_indices[j];
if (!isNaN(values[row][column])) {
sum += values[row][column];
count++;
}
}
values[average_index][column] = sum / count;
} else if (stat == 'Column Comparisons')
values[average_index][column] = '-'; // Set hyphen to indicate that this cell is not being tested.
}
}
table.set(stat, values);
});
}
}
// This script is designed to calculate the effective column n for each column in a
// table. If this statistic is the same within each column then the table will display
// a single number. If there are a range of effective column n then the table will
// show maximum and minimum values.
// Effective Column n is defined as Column n / Base n * Effective Base n
// This statistic is available for crosstabs and tables showing Pick Any - Grid questions
// (ie any table that allows a 'Column n' to be shown in the statistics). If the table
// is not of an appropriate type then no modification will be made to the table.
// Where possible, the effective column n will be calculated using the Column n values
// from the statistics cells. In for tables where this is not available it will be
// calculated using the Column n from Statistics - Below.
function addEffectiveColumnN() {
const stat_name = table.getTranslation('Column n');
const in_displayr = inDisplayr();
const statistic_text = in_displayr ? "Statistics >" : "Statistics - ";
let rule_name = 'Adding Rows to Display the Effective ' + stat_name;
form.setHeading(rule_name);
let summary_text = rule_name.charAt(0) + rule_name.slice(1).toLowerCase();
form.setSummary(summary_text);
let description = form.newLabel('Computes an estimate of the Effective ' + stat_name + ' for each column.');
description.lineBreakAfter = true;
form.setInputControls([description]);
// Specify the threshhold for numerical precision (used for the purpose of figuring out
// whether the new statistic varies within each column)
let eps = 1e-3;
// Determine which statistic to use to store the Effective Column n figures. The default
// option is to use the first statistic in the table that is not a percentage (except Base n),
// or if no such statistic is available the first statistic will be used.
// If you wish to use an alternative statistic, then enter the name of the statistic in quotes
// in the variable called override_stat
let override_stat = '';
let stat_to_use;
let not_allowed_stats = ['Base n', 'Column Names', 'Columns Compared', 'Column Comparisons'];
if (override_stat.length === 0) {
stat_to_use = table.statistics[0];
for (let j = 0; j < table.statistics.length; j++) {
if (table.statistics[j].indexOf('%') === -1 && not_allowed_stats.indexOf(table.statistics[j]) === -1) {
stat_to_use = table.statistics[j];
break;
}
}
} else {
stat_to_use = override_stat;
}
let redo_percentages = stat_to_use.indexOf("%") > -1;
// Check if this table is appropriate for displaying the Effective Column n
if (table.numberColumns == 1)
form.ruleNotApplicable('it requires a table with more than one column');
if (table.availableStatistics.indexOf('Column n') === -1 && typeof below_table === 'undefined')
form.ruleNotApplicable('it requires a table with Column in the the ' + statistic_text + ' Cells or ' + statistic_text + ' Below');
// Obtain the Effective Base n from Statistics - Cells
let effective_base_n = table.get('Effective Base n');
// Obtain the Base n from Statistics - Cells
let base_n = table.get('Base n');
// Obtain the Column n from the Statistics - Cells, or from
// the Statistics - Below if the table shows a Pick Any - Grid
let column_n;
if (table.availableStatistics.indexOf('Column n') > -1)
column_n = table.get('Column n');
else {
column_n = table.get('Base n'); // Dummy array to fill with column n numbers
let below_column_n = below_table.get('Column n');
for (let row = 0; row < table.numberRows; row ++) {
for (let col = 0; col < table.numberColumns; col ++) {
column_n[row][col] = below_column_n[col][0];
}
}
}
// Create an array to store Effective Column n
let effective_column_n = table.get('Effective Base n');
// Loop through the cells of the array and calculate
// the Effective Column n for each cell in the table
// Fix percent signs
let cell_text = table.cellText;
let percent_array = table.statistics.map(function (stat) { return (stat.indexOf('%') > -1 ? '%' : ''); });
for (let col = 0; col < table.numberColumns; col++) {
for (let row = 0; row < table.numberRows; row++) {
effective_column_n[row][col] = effective_base_n[row][col]*column_n[row][col]/base_n[row][col];
if (redo_percentages)
cell_text[row][col] = percent_array;
}
}
if (redo_percentages) {
table.showPercentSign = false;
table.cellText = cell_text;
}
// Determine the maximum and minium Effective Column n
// in each column
let min_array = columnMinimums(effective_column_n);
let max_array = columnMaximums(effective_column_n);
// Work out whether we need to add one or two rows to the
// table and make the appropriate modifications
let values_to_replace;
let adding_one_row = constantWithinColumns(min_array, max_array, eps);
if (adding_one_row) {
// Effective Column n is constant within each column so add
// a single row and insert the numbers
let index = table.numberRows - 1;
table.insertRowAfter(index, 'Effective ' + stat_name);
if (typeof right_table !== 'undefined') { // Add a new row for the right table
if (right_table.columnLabels == null)
right_table.insertRowAfter(index, 'Effective ' + stat_name);
else
right_table.insertColumnAfter(index, 'Effective ' + stat_name);
}
values_to_replace = table.get(stat_to_use);
for (let col = 0; col < table.numberColumns; col ++)
values_to_replace[table.numberRows - 1][col] = min_array[col];
} else {
// Effective Column n varies within each column, so add
// one row to display the minimum and one for the maximum
let index = table.numberRows - 1;
table.insertRowAfter(index, 'Minimum Effective ' + stat_name);
table.insertRowAfter(index, 'Maximum Effective ' + stat_name);
if (typeof right_table !== 'undefined') { // Add a new row for the right table
if (right_table.columnLabels == null) {
right_table.insertRowAfter(index, 'Minimum Effective ' + stat_name);
right_table.insertRowAfter(index, 'Maximum Effective ' + stat_name);
} else {
right_table.insertColumnAfter(index, 'Minimum Effective ' + stat_name);
right_table.insertColumnAfter(index, 'Maximum Effective ' + stat_name);
}
}
values_to_replace = table.get(stat_to_use);
for (let col = 0; col < table.numberColumns; col ++) {
values_to_replace[table.numberRows - 1][col] = min_array[col];
values_to_replace[table.numberRows - 2][col] = max_array[col];
}
}
// Set the new numbers into the statistic stat_to_use
table.set(stat_to_use, values_to_replace);
// Show missing values as blank in order to keep the new
// rows tidy
table.showMissingAs('');
}
// Function to check whether the column maximums (max_array) are the same
// as the column miniumums (min_array) within the precision specified by
// eps
function constantWithinColumns(min_array, max_array, eps) {
let constant_within_columns = true;
for (let j = 0; j < min_array.length; j++) {
if (Math.abs(max_array[j] - min_array[j]) > eps) {
constant_within_columns = false;
break;
}
}
return constant_within_columns;
}
// This function returns an array containing the minimum of each
// column in the input staistic_array.
// This function assumes that statistic_array has the structure of
// an array derived from the statistics - cells of a table.
// Will return an NaN for any column containing NaNs.
function columnMinimums(statistic_array) {
let min_array = [];
for (let col = 0; col < statistic_array[0].length; col++) {
let min = statistic_array[0][col];
for (let row = 0; row < statistic_array.length; row++) {
let cur_val = statistic_array[row][col];
if (cur_val < min)
min = cur_val;
if (isNaN(cur_val))
min = NaN;
}
min_array.push(min);
}
return min_array;
}
// This function returns an array containing the Maximum of each
// column in the input staistic_array.
// This function assumes that statistic_array has the structure of
// an array derived from the statistics - cells of a table.
// Will return an NaN for any column containing NaNs.
function columnMaximums(statistic_array) {
let max_array = [];
for (let col = 0; col < statistic_array[0].length; col++) {
let max = statistic_array[0][col];
for (let row = 0; row < statistic_array.length; row++) {
let cur_val = statistic_array[row][col];
if (cur_val > max)
max = cur_val;
if (isNaN(cur_val))
max = NaN;
}
max_array.push(max);
}
return max_array;
}