Modify Whole Table or Plot - Show Statistics Above

This rule moves the marginal statistics in Statistics - Below so that they appear at the top of the table. You can choose to either move all statistics that are currently selected in Statistics - Below, or to choose a subset of statistics. New rows are created at the top of the table for each statistic, and the corresponding rows will be removed from the bottom of the table. You also have the option to shade the new rows which are added (which helps set them apart from normal table rows), and you can also choose to rename the rows for particular statistics.

This rule requires Q version 5.2 or later. If you do not have this version, contact support@q-researchsoftware.com to inquire about an upgrade.

Options

The options screen for this rule looks as follows:

Shade rows Adds color to the new rows in the table. When selected you will be able to choose a Primary color and Secondary color. The rows in the table will be shaded alternately.

Rename statistics Use this option to override the row names for statistics you wish to place at the top of the table. For example, with the selection above, the row which would normally be called the Average will instead be called Mean.

Always show sample size When this option is ticked, the rule will determine the most appropriate sample size statistic for the columns of each table (either Column n or Base n) and add it as the first new row above the main table. This means that if some of your tables should show Column n and some others should show Base n you can apply this rule once to all of the tables and it will intelligently choose the right statistic for each table.

Choose statistics to place at top When this option is ticked you can choose which statistics to move, and those statistics will be shown in the order that you choose. When this option is not ticked, all statistics from Statistics - Below will be migrated to the top of the table. The order of the rows will be the opposite of the order in which the rows are normally shown in Statistics - Below.

Examples

Technical details

This rule uses a number of tricks to move the numbers around in the table, and as a result there are some limitations to be aware of:

• This rule should not be used when generating charts by exporting to Office, or using Q's built-in charts. The formatting is not compatible with charts.
• When exported to Excel, the new rows will only contain as many decimal places as shown in the table in Q. This is different to the numbers from normal table cells, which always export with all of the decimal places that are available, even though only one or two may be shown (depending on what you show in Q).
• When exported to Excel, the new rows will occupy as many rows in Excel as there are statistics selected in Statistics - Cells. This means that if your table contains a number of statistics in Statistics - Cells, there will be several blank rows at the top of your Excel export.

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

▶ Show Code

includeWeb('Table JavaScript Utility Functions');
includeWeb('JavaScript Utilities');

form.setSummary("Show Statistics Above");
if (Q.fileFormatVersion() <= 10.9) {
    alert("Show Statistics Above requires Q version 5.2 or later.  For access, contact support@q-researchsoftware.com");
    form.ruleNotApplicable("it requires an upcoming version of Q");
}

table.requireOriginalRowsColumns();
if (!belowTableExists())
    form.ruleNotApplicable("this table does not have Statistics - Below");



var not_selected_string = "(none)";
var _GLOBAL_STAT_BELOW_LIST = ["Average",
                               "Sum",
                               "Column n",
                               "Column Population",
                               "Base n",
                               "Base Population",
                               "Column Comparisons",
                               "Column Names",
                               "Columns Compared",
                               "5th Percentile",
                               "25th Percentile",
                               "75th Percentile",
                               "95th Percentile",
                               "Column Standard Error",
                               "Corrected p",
                               "Correlation",
                               "Cumulative %",
                               "d.f.",
                               "Effective n",
                               "Effective Base n",
                               "Expected Correlation",
                               "Interquartile Range",
                               "Lower Confidence Interval",
                               "Maximum",
                               "Median",
                               "Minimum",
                               "Missing n",
                               "Mode",
                               "Multiple Comparison Adjustment",
                               "p",
                               "Standard Deviation",
                               "Standard Error",
                               "Trimmed Average",
                               "Upper Confidence Interval",
                               "z-Statistic"];

table.showMissingAs("");


// Controls for shading the cells of the new rows
var shade_stats_checkbox = form.newCheckBox("doShade", "Shade rows");
shade_stats_checkbox.setDefault(false);
shade_stats_checkbox.lineBreakAfter = true;
var form_controls = [shade_stats_checkbox];
form.setInputControls(form_controls);

if (shade_stats_checkbox.getValue()) {
    var primary_color_label = form.newLabel("Primary color");
    var primary_shade_color_picker = form.newColorPicker("primary");
    //primary_shade_color_picker.lineBreakAfter = true;
    primary_shade_color_picker.setDefault([235, 235, 235]);

    var secondary_color_label = form.newLabel("Secondary color");
    var secondary_shade_color_picker = form.newColorPicker("secondary");
    secondary_shade_color_picker.lineBreakAfter = true;
    secondary_shade_color_picker.setDefault([245, 245, 245]);
    form_controls = form_controls.concat([primary_color_label,primary_shade_color_picker, secondary_color_label, secondary_shade_color_picker]);
    form.setInputControls(form_controls);    
}

// Controls for allowing the user to rename statistics
var do_renaming_box = form.newCheckBox("formRename", "Rename statistics");
do_renaming_box.setDefault(false);
do_renaming_box.lineBreakAfter = true;
form_controls.push(do_renaming_box);
form.setInputControls(form_controls);
var rename_obj = [];
if (do_renaming_box.getValue()) {
    var counter = 0;
    var remaining_stats = _GLOBAL_STAT_BELOW_LIST.slice();
    var translated_stats = translateStats(remaining_stats);
    var stat_label = form.newLabel("Statistic");
    var stat_box = form.newComboBox("sb"+counter.toString(),
				    [not_selected_string].concat(translated_stats))
    stat_box.setDefault(not_selected_string);
    var name_label = form.newLabel("New name");
    var name_box = form.newTextBox("nb"+counter.toString());
    name_box.lineBreakAfter = true;
    form_controls = form_controls.concat([stat_label,
                                          stat_box,
                                          name_label,
                                          name_box]);
    form.setInputControls(form_controls);
    var selected_translated = stat_box.getValue();
    var position, last_selected_stat;
    //alert(last_selected_stat != not_selected_string);
    while (selected_translated != not_selected_string) {
	position = translated_stats.indexOf(selected_translated);
	last_selected_stat = remaining_stats[position];
        rename_obj.push({stat: last_selected_stat, name: name_box.getValue()});
        counter++;
        remaining_stats.splice(position, 1);
	translated_stats.splice(position,1);
        //alert("sb"+counter.toString());
        var stat_box = form.newComboBox("sb"+counter.toString(),
					[not_selected_string].concat(translated_stats));
        stat_box.setDefault(not_selected_string);
        var name_box = form.newTextBox("nb"+counter.toString());
        name_box.lineBreakAfter = true;
        form_controls = form_controls.concat([stat_label,
                                              stat_box,
                                              name_label,
                                              name_box]);
        form.setInputControls(form_controls);
        selected_translated = stat_box.getValue();
        //alert(last_selected_stat != not_selected_string);
    }
}

var sample_size_box = form.newCheckBox("formAlways",
				       "Always show sample size " +
				       "(whichever is appropriate for each table)");
sample_size_box.setDefault(false);
sample_size_box.lineBreakAfter = true;
form_controls.push(sample_size_box);
form.setInputControls(form_controls);

// Controls for manually selecting which statistics get moved to the top
var choose_stats_box = form.newCheckBox("formDoAll", "Choose statistics to place at top");
choose_stats_box.setDefault(false);
choose_stats_box.lineBreakAfter = true;
form_controls.push(choose_stats_box);
form.setInputControls(form_controls);
var stats_to_show = [];
if (choose_stats_box.getValue()) {
    var counter = 0;
    var remaining_stats = _GLOBAL_STAT_BELOW_LIST.slice();
    var translated_stats = translateStats(remaining_stats);
    var selected_stat_label = form.newLabel("Show at the top:");
    selected_stat_label.lineBreakAfter = true;
    var show_stat_box = form.newComboBox("ssb" + counter,
					 [not_selected_string].concat(translated_stats));
    show_stat_box.lineBreakAfter = true;
    show_stat_box.setDefault(not_selected_string);
    form_controls = form_controls.concat([selected_stat_label, show_stat_box]);
    form.setInputControls(form_controls);
    var selected_translated = show_stat_box.getValue();
    var selected_stat;
    while (selected_translated != not_selected_string) {
	position = translated_stats.indexOf(selected_translated);
	selected_stat = remaining_stats[position];	
        stats_to_show.push(selected_stat);
        remaining_stats.splice(position,1);
        translated_stats.splice(position,1);	
        counter ++;
        var show_stat_box = form.newComboBox("ssb" + counter,
					     [not_selected_string].concat(translated_stats));
        show_stat_box.lineBreakAfter = true;
        show_stat_box.setDefault(not_selected_string);
        form_controls.push(show_stat_box);
        form.setInputControls(form_controls);
	selected_translated = show_stat_box.getValue();
    }
    stats_to_show = intersection(stats_to_show, below_table.availableStatistics);
    stats_to_show.reverse();
} else
    stats_to_show = below_table.statistics;

// Work out the appropriate sample size stat for this table and add it to the list of stats if not there already
if (sample_size_box.getValue()) {
    // Checking to see where column n is available
    var use_column_n = false; 
    var use_base_n = false;
    var blue_type = table.blueQuestion.questionType;
    var blue_numeric_1d = blue_type == "Number" || blue_type == "Number - Multi";
    var brown_type = table.brownQuestion.questionType;
    var brown_numeric_1d = brown_type == "Number" || brown_type == "Number - Multi" ;
    
     
    if ((blue_type ==  "Number - Grid" || blue_type ==  "Pick Any - Grid") && table.brownQuestion == "SUMMARY")
        use_base_n = true;
    else if (blue_numeric_1d && !brown_numeric_1d)
        use_column_n = true;
    else {
        use_column_n = below_table.availableStatistics.indexOf('Column n') != -1;  
    }

    if (use_column_n && stats_to_show.indexOf("Column n") == -1)
        stats_to_show.splice(0, 0, "Column n");

    if (use_base_n && stats_to_show.indexOf("Base n") == -1)
        stats_to_show.splice(0, 0, "Base n");
}

// Add rows to the top of the table
stats_to_show.forEach(function (stat) {
    var name_to_use = table.getTranslation(stat);
    if (do_renaming_box.getValue()) {
        var renamed = rename_obj.filter(function (obj) { return obj.stat == stat; });
        if (renamed.length > 0)
            name_to_use = renamed[0].name;
    }
    table.insertRowAfter(null, name_to_use);
});


//  Color cells for new rows
if (shade_stats_checkbox.getValue()) {
    var cell_colors = table.cellColors;
    var selected_primary_color = primary_shade_color_picker.getValue();
    var primary_color_row = cell_colors[0].map(function (x) { return selected_primary_color; });
    var selected_secondary_color = secondary_shade_color_picker.getValue();
    var secondary_color_row = cell_colors[0].map(function (x) { return selected_secondary_color; });
    stats_to_show.forEach(function (stat, ind) {
        if (ind % 2 == 0)
            cell_colors[ind] = primary_color_row;
        else
            cell_colors[ind] = secondary_color_row;
    });
    table.cellColors = cell_colors;
}





// Copy values as cell text
// Copy significance highlighting too
var cell_text = table.cellText;
var cell_fonts = table.cellFontColors;
var cell_arrows = table.cellArrows;
var cell_significance = table.cellSignificance;

var below_fonts = below_table.cellFontColors;
var below_arrows = below_table.cellArrows;
var below_significance = below_table.cellSignificance;

if (below_fonts[0].length == 1) {
    below_fonts = below_fonts.map(function (x) { return x[0]});
    below_arrows = below_arrows.map(function (x) { return x[0]});
    below_significance = below_significance.map(function (x) { return x[0]});
} else {
    below_fonts = below_fonts[0];
    below_arrows = below_arrows[0];
    below_significance = below_significance[0];
}

stats_to_show.forEach(function (stat, ind) {
    var new_row_ind = stats_to_show.length - ind - 1;
    var values = below_table.get(stat);
    if (values[0].length == 1)
        values = [values.map(function (x) { return x[0]; })];
    var translation = getStatisticTranslationString(stat);
    var digits = table.decimalPlaces[stat == table.getTranslation(stat) ? stat : translation];
    if (_COLUMN_COMPARISON_STATS.indexOf(stat) == -1)
        values = formatValues(values, digits, table.showPercentSign && _PERCENT_STATS.indexOf(stat) > -1);
    values = values[0].map(function (x) { return [x]; });
    cell_text[new_row_ind] = values;
    if (_HIGHLIGHTED_BELOW_STATS.indexOf(stat) > -1) {
        cell_fonts[new_row_ind] = below_fonts;
        cell_arrows[new_row_ind] = below_arrows;
        cell_significance[new_row_ind] = below_significance;
    }
});

table.cellFontColors = cell_fonts;
table.cellArrows = cell_arrows;
table.cellSignificance = cell_significance;





// Handle % Signs for main table cells. This allows us to turn off the % signs globally and
// then add them in where appropriate.
if (table.showPercentSign) {
    table.showPercentSign = false;
    var percent_text = table.statistics.map(function (stat) { return _PERCENT_STATS.indexOf(stat) == -1 ? "" : "%"; } );
    for (var row = stats_to_show.length; row < table.numberRows; row++) {
        for (var col = 0; col < table.numberColumns; col++) {
            cell_text[row][col] = percent_text;
        }
    }
}


// Place new text-based values
table.cellText = cell_text;


// Turn off stats below
if (!choose_stats_box.getValue())
    below_table.statistics = [];
else {
    below_table.statistics = difference(below_table.statistics, stats_to_show)
    // Set translations for any below stats the user requested be renamed but
    //   not selected to be shown above
    if (do_renaming_box.getValue() && below_table.statistics.length > 0) { 
        var rename_below = rename_obj.filter(function(o) { return below_table.statistics.includes(o.stat); });
        for (var i = 0; i < rename_below.length; i++)
            form.setTranslation(rename_below[i].stat,rename_below[i].name);
    }
}

function formatValues(array, num_decimals, add_percent) {
    includeWeb('JavaScript Utilities');
    if (num_decimals !== parseInt(num_decimals, 10))
        throw new Error("Expected an integer value.");
    if (num_decimals < 0)
        throw new Error("Expected a non-negative value.");
 
    var new_values = array.map(function (a) {
        return a.map(function (b) {
            if (isNaN(b))
                return "";

            var rounded_value = roundDecimalNumber(b, num_decimals);
            rounded_value = rounded_value.toString();
            if (num_decimals > 0 && rounded_value.indexOf(".") == -1) { //Rounding has removed the decimal places
                rounded_value = rounded_value + ".";
                for (var j = 0; j < num_decimals; j++)
                    rounded_value = rounded_value + "0";
            }
            if (add_percent)
                rounded_value = rounded_value + "%";
            return rounded_value;
        });
    });
    return new_values;
}

function translateStats(stats) {
    return stats.map(function(s) {
	try {
	    return table.getTranslation(s);
	}catch(e) {
	    return s;
      }
    });
}

See also

• User Input for Rules for technical information on Rules.
• Rule Online Library for other examples of Rules.
• Table JavaScript and Plot JavaScript for the JavaScript that is used to write custom rules.
• JavaScript for information about the JavaScript programming language.