Table Computations - Add Rows to Display Effective Column n

This rule computes an estimate of Effective Column n for each column and displays it on the table. Where the cells in the column have different values of Column n, a range is shown (see the example).

Example

Technical details

Effective Column n is computed using:

Effective Column n = Column n / Base n * Effective Base n

Where the Effective Column n is the same within the cells of each column, the table will gain a single row at the bottom that gives the result for each column. When the statistic varies within the columns of the table, then the table will gain two extra rows, one for the minimum value of the Effective Column n, and one for the maximum value. Where the table cannot display the Column n within the Statistics - Cells, the calculation instead uses the values of the Column n for each column from the Statistics - Below.

It may be necessary to turn off the % signs in your table to make the best use of this script. This is because the script displays the Effective Base n in a statistic that is already selected in the Statistics - Cells. The script will try to use a non-% statistic (e.g. n or Column n) to display the Effective Column n, but will choose a % statistic (eg % or Column %) if no non-% statistics are selected on the table.

The Effective Column n will not necessarily 'add up' unless:

Weights and significance is set to Kish's approximation or a specific value, and
There is no missing data.

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

// 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.
 
form.setSummary("Computations - Adding Rows to Display the Effective Column n");
 
// Specify the threshhold for numerical precision (used for the purpose of figuring out
// whether the new statistic varies within each column)
var 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
var override_stat = '';
var not_allowed_stats = ['Base n', 'Column Names', 'Columns Compared', 'Column Comparisons'];
if (override_stat.length == 0) {
    var stat_to_use = table.statistics[0];
    for (var 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;
}
 
 
// 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");
 
} else if (table.availableStatistics.indexOf('Column n') == -1 && typeof below_table == "undefined") {

    form.ruleNotApplicable("it requires a table with Column in the the Statistics - Cells or Statistics - Below");

} else {
 
    // Obtain the Effective Base n from Statistics - Cells
    var effective_base_n = table.get('Effective Base n');
 
    // Obtain the Base n from Statistics - Cells
    var 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
    var 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
        var below_column_n = below_table.get('Column n');
        for (var row = 0; row < table.numberRows; row ++) {
            for (var col = 0; col < table.numberColumns; col ++) {
                column_n[row][col] = below_column_n[col][0];
            }
        }
    }
 
 
    // Create an array to store Effective Column n
    var 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
    for (var col = 0; col < table.numberColumns; col++) {
        for (var row = 0; row < table.numberRows; row++) {
            effective_column_n[row][col] = effective_base_n[row][col]*column_n[row][col]/base_n[row][col];
        }
    }
 
 
    // Determine the maximum and minium Effective Column n
    // in each column
    var min_array = columnMinimums(effective_column_n);
    var 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
    if (constantWithinColumns(min_array, max_array, eps)) {
 
        // Effective Column n is constant within each column so add
        // a single row and insert the numbers
        var index = table.numberRows - 1;
        table.insertRowAfter(index, "Effective Column n");
        if (typeof right_table !== 'undefined') { // Add a new row for the right table
            if (right_table.columnLabels == null)
                right_table.insertRowAfter(index, "Effective Column n");
            else
                right_table.insertColumnAfter(index, "Effective Column n");
        }
        var values_to_replace = table.get(stat_to_use);
        for (var 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
        var index = table.numberRows - 1;
        table.insertRowAfter(index, "Minimum Effective Column n");
        table.insertRowAfter(index, "Maximum Effective Column n");
        if (typeof right_table !== 'undefined') { // Add a new row for the right table
            if (right_table.columnLabels == null) {
                right_table.insertRowAfter(index, "Minimum Effective Column n");
                right_table.insertRowAfter(index, "Maximum Effective Column n");
            } else {
                right_table.insertColumnAfter(index, "Minimum Effective Column n");
                right_table.insertColumnAfter(index, "Maximum Effective Column n");
            }
        }
        var values_to_replace = table.get(stat_to_use);
        for (var 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) {
    var constant_within_columns = true;
    for (var 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) {
    var min_array = [];
    for (var col = 0; col < statistic_array[0].length; col++) {
        var min = statistic_array[0][col];
        for (var row = 0; row < statistic_array.length; row++) {
            var 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) {
    var max_array = [];
    for (var col = 0; col < statistic_array[0].length; col++) {
        var max = statistic_array[0][col];
        for (var row = 0; row < statistic_array.length; row++) {
            var 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;
}

Table Computations - Add Rows to Display Effective Column n

Example

Technical details

How to apply this rule

For the first time in a project

Additional applications of the rule

Removing the rule

How to modify the rule

JavaScript

See also

Navigation menu

Table Computations - Add Rows to Display Effective Column n

Example

Technical details

How to apply this rule

For the first time in a project

Additional applications of the rule

Removing the rule

How to modify the rule

JavaScript

See also

Navigation menu

Search