Significance Testing in Tables - Color Comparisons with Specific Column

From Q
Jump to: navigation, search

This rule uses the Column Comparisons to add color to cells which have a significant relationship to a particular column. This can be used, for example, to color any cells whose Column Comparisons letters indicate that the cell is significantly higher or lower than the NET. The rule will replace the column letters with colors when comparing against the target column, but will maintain the letters for other comparisons. This rule works within the spans of the table, so that if you wish to highlight comparisons with the NET or other column within each of the spans on your table, then this is done automatically.


In the following table, we have used this rule to highlight results which are significant compared with the results in the NET column. In the options we have specified orange for cells which are significantly low, and yellow for cells that are significantly high. Note that the Column Comparisons letters remain for comparisons that do not include the NET column.


Technical details

The Column Comparisons do not test with the NET column(s) by default, and in order to include the NET in the testing you must change the settings according to How to Include the Main NET Column in Column Comparisons.

This rule will not show the appropriate high and low results if Show Redundant Tests is enabled in Statistical Assumptions at the table or project options level.

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.


  • 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).


includeWeb("Table JavaScript Utility Functions");
includeWeb("JavaScript Array Functions");
includeWeb("JavaScript Utilities");

if (table.availableStatistics.indexOf("Column Names") == -1)
    form.ruleNotApplicable("column comparisons not available on this table");

// table.hypothesisTestingEnabled = false; // alpha button disabled

// Setting up the form
form.setHeading("Highlight comparisons with a particular column");
var column_choice_label = form.newLabel("Column to compare with: (e.g. NET)");
var column_name_box = form.newTextBox("cn");
column_name_box.lineBreakAfter = true;
var high_label = form.newLabel("Higher results:");
var high_color_picker = form.newColorPicker("hcolor");
high_color_picker.lineBreakAfter = true;
var low_label = form.newLabel("Lower results:");
var low_color_picker = form.newColorPicker("lcolor");
low_color_picker.lineBreakAfter = true;
form.setInputControls([column_choice_label, column_name_box, high_label, high_color_picker, low_label, low_color_picker]);

var target_label = column_name_box.getValue();
form.setSummary("Color cells which are significant compared with: " + target_label);
var high_color = high_color_picker.getValue();
var low_color = low_color_picker.getValue();

// Get the column spans at the lowest level

var lowest_col_spans = getLowestLevelSpans(table.columnSpans)

function getLowestLevelSpans(spans) {
    var lowest_spans = [];
    var 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);
    return lowest_spans;

if (lowest_col_spans.length == 0) {
    var span_indices = [];
    for (var j = 0; j < table.numberColumns; j++)
    lowest_col_spans = [{label: "the columns", indices: span_indices}];

var do_below = false;
if (belowTableExists()) {
    do_below = below_table.availableStatistics.indexOf("Column Comparisons") > -1 && intersection(below_table.statistics, _HIGHLIGHTED_BELOW_STATS).length > 0;    
    if (do_below) {
        var below_column_comparisons = below_table.get("Column Comparisons");
        var below_colors = below_table.cellColors;
        var below_transposed = below_column_comparisons.length > 1;

// Loop over each span and make the necessary modifications
var cell_colors = table.cellColors;
var column_letters = table.get("Column Names");
var column_comparisons = table.get("Column Comparisons");
var column_labels = table.columnLabels;
lowest_col_spans.forEach(function (span) {
    var span_labels = (x) { return column_labels[x]; });
    var matching_labels = span_labels.filter(function (label) { return label == target_label; });
    if (matching_labels.length > 1)
        form.ruleNotApplicable("column " + target_label + " appears more than once in " + span.label);
    // Unique match, do the modifications
    if (matching_labels.length == 1) {
        var target_index = span.indices[span_labels.indexOf(target_label)];
        var target_letter = column_letters[0][target_index].toLowerCase();
        span.indices.forEach(function (col) {
            if (col != target_index) {
                for (var row = -1; row < table.numberRows; row++) {
                    // Current result is high
                    if (row == -1) {
                        if (do_below) {
                            var current_r = below_transposed ? col : 0;
                            var current_c = below_transposed ? 0 : col;
                            var target_r = below_transposed ? target_index : 0;
                            var target_c = below_transposed ? 0 : target_index;
                            if (below_column_comparisons[current_r][current_c].toLowerCase().indexOf(target_letter) > -1) {
                                below_colors[current_r][current_c] = high_color;
                                var replace_regexp = new RegExp(target_letter, 'i')
                                below_column_comparisons[current_r][current_c] = below_column_comparisons[current_r][current_c].replace(replace_regexp, "");
                            } else if (below_column_comparisons[target_r][target_c].toLowerCase().indexOf(column_letters[0][col].toLowerCase()) > -1) {
                                below_colors[current_r][current_c] = low_color;    
                    } else {
                        if (column_comparisons[row][col].toLowerCase().indexOf(target_letter) > -1) {
                            cell_colors[row][col] = high_color;
                            var replace_regexp = new RegExp(target_letter, 'i')
                            column_comparisons[row][col] = column_comparisons[row][col].replace(replace_regexp, "");
                        } else if (column_comparisons[row][target_index].toLowerCase().indexOf(column_letters[row][col].toLowerCase()) > -1) {
                            cell_colors[row][col] = low_color;

        // Blank out comparisons in target column
        for (var row = 0; row < table.numberRows; row++) {
            column_comparisons[row][target_index] = "";
        if (do_below){
             below_column_comparisons[below_transposed ? target_index : 0][below_transposed ? 0 : target_index] = "";

table.cellColors = cell_colors;
table.set("Column Comparisons", column_comparisons);
if (do_below) {
    below_table.cellColors = below_colors;
    below_table.set("Column Comparisons", below_column_comparisons);

See also