Introduction
This article describes how to go from a crosstab with categorical questions...
...to a table that displays the upper and lower confidence intervals:
Requirements
This example may be used to compute confidence intervals for crosstabs where there is a categorical question (Pick One or Pick Any) selected in the Blue and Brown drop-down menu. The rule borrows statistics from the table (by default it takes over the Index and Missing n statistics), and renames them to Upper Confidence Interval and Lower Confidence Interval respectively.
Method
1. Select your table.
2. Go to Automate > Custom Rule.
3. Paste the below into the dialog:
// Choose which stats to replace based on those available for categorical data
var upper_statistic_to_replace = "Index";
var lower_statistic_to_replace = "Missing n";
// Confirm the table is a crosstab using categorical data
if (table.availableStatistics.indexOf("Column %") == -1) {
table.suppressOutput('Confidence intervals not computed - it only applies to tables containing Column % in the Statistics - Cells');
form.ruleNotApplicable('it only applies to tables containing Column % in the Statistics - Cells');
}
// Confirm the categorical data used is either pick one or pick any -- other types not supported
if (["Pick One", "Pick Any"].indexOf(table.blueQuestion.questionType) == -1 || ["Pick One", "Pick Any"].indexOf(table.brownQuestion.questionType) == -1){
table.suppressOutput('Confidence intervals not computed - the table should have a Nominal or Binary-Multi variable set selected in both the Rows and Columns of the table.');
form.ruleNotApplicable('the table should have a Nominal or Binary-Multi variable set selected in both the Rows and Columns of the table.');
}
//add titles to rule
form.setSummary('Calculate confidence intervals on crosstabs with percentages');
form.setHeading('Calculate confidence intervals on crosstabs with percentages');
// Set up controls for user input to select the significance level
var label = form.newLabel('Significance level:');
var p_level = form.newNumericUpDown('Lower'); // create numeric control
p_level.setIncrement(0.01);
p_level.setDefault(0.05);
p_level.setMaximum(1);
p_level.setMinimum(0);
p_level.lineBreakAfter = true;
let t_box = form.newCheckBox('uset', 'Use Survey Reporter / Quantum formula');
t_box.setDefault(false);
form.setInputControls([label, p_level, t_box]); // add controls to settings shown
// get the value from the input form above and set it as the cutoff variable
var p_cutoff = p_level.getValue();
let use_t_dist = t_box.getValue();
// Get relevant table stats
var n = table.get("n");
var col_n = table.get("Column n");
var col_percent = table.get("Column %");
var col_se = table.get("Column Standard Error");
var upper_values = table.get(upper_statistic_to_replace);
var lower_values = table.get(lower_statistic_to_replace);
var isWeight = table.weight != null;
// Do the calculations on each cell in the table
for (var row = 0; row < table.numberRows; row++) {
for (var col = 0; col < table.numberColumns; col++) {
if (use_t_dist) {
let df = col_n[row][col] - 1;
let t = jStat.studentt.inv(p_cutoff/2, df);
let increment = t * col_se[row][col] * 100;
lower_values[row][col] = col_percent[row][col] + increment;
upper_values[row][col] = col_percent[row][col] - increment;
} else {
var z = jStat.normal.inv(p_cutoff/2,0,1);
if (isWeight) {
var increment = z*col_se[row][col]*100;
lower_values[row][col] = col_percent[row][col] + increment;
upper_values[row][col] = col_percent[row][col] - increment;
} else {
var calc1 = col_n[row][col] + Math.pow(z,2);
var calc2 = (n[row][col] + Math.pow(z,2)/2)/calc1;
var calc3 = Math.sqrt(calc2*(1-calc2)/calc1);
var increment = z*calc3;
lower_values[row][col] = (calc2 + increment)*100;
upper_values[row][col] = (calc2 - increment)*100;
}
}
}
}
//set the values of the statistics we're overwriting to the new values on the table
table.set(lower_statistic_to_replace, lower_values);
table.set(upper_statistic_to_replace, upper_values);
//see if statistics for upper and lower CI are showing on table and if not, add them
var stats = table.statistics;
if(stats.indexOf(upper_statistic_to_replace) == -1) stats.push(upper_statistic_to_replace);
if(stats.indexOf(lower_statistic_to_replace) == -1) stats.push(lower_statistic_to_replace);
table.statistics = stats;
//rename the statistics we're overwriting to upper and lower CI
form.setTranslation(upper_statistic_to_replace, "Upper Confidence Interval");
form.setTranslation(lower_statistic_to_replace, "Lower Confidence Interval");
3. OPTIONAL: Change the statistics to replace from Index and Missing n, if desired.
4. Press the blue Play button > Close.
5. Choose your Significance level. By default this is 0.05 to match Q's default Overall Significance Level setting, but if you are testing at a different level then change this number.
6. Tick Use Survey Reporter / Quantum formula option if you have Proportions test in your Statistical Assumptions set to either Survey Reporter or Quantum.
7. Press OK > OK.
Note the following:
- This Rule applies one of two the formulas from the page Confidence Interval, depending on whether or not the table is weighted:
- When the table is weighted, the rule applies the formula from the section called Default confidence intervals. This which uses the quantiles of the normal distribution.
- When the table is not weighted, this rule applies the formula described in the section Confidence intervals for percentages with unweighted samples. This is the Agresti-Coull interval.
- When Proportions test is set to either Survey Reporter or Quantum, the test then becomes a type of t-test and so uses the default t-distribution version of the formula.
- Note, the formula under Confidence intervals where Weights and significance has been set to Un-weighted sample size in tests is not currently implemented, and users should contact Support if they need to take this into account.
- The rule uses the Column %, Column Standard Error, n, and Column n statistics on your table.
- This Rule uses the jStat JavaScript library to compute the quantiles of the normal distribution.
- The Rule does not know about statistical settings at the project or table level.
See Also
How to Make One Column Appear After Another in a Table
How to Automatically Rename Row Labels in Tables
How to Hide Empty Rows and Columns in a Table
How to Add an Average of Each Row to a Table