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.
var upper_statistic_to_replace;
var lower_statistic_to_replace;
// Categorical results
if (table.availableStatistics.indexOf("Column %") > -1) {
var upper_statistic_to_replace = "Index";
var lower_statistic_to_replace = "Missing n";
} else {
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');
}
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 Pick One or Pick Any question selected in the Blue and Brown drop-down menus.');
form.ruleNotApplicable('the table should have a Pick One or Pick Any question selected in the Blue and Brown drop-down menus.');
}
// Set up controls for user input.
form.setSummary('Calculate confidence intervals on crosstabs with percentages');
form.setHeading('Calculate confidence intervals on crosstabs with percentages');
var label = form.newLabel('Significance level:');
var p_level = form.newNumericUpDown('Lower');
p_level.setIncrement(0.01);
p_level.setDefault(0.05);
p_level.setMaximum(1);
p_level.setMinimum(0);
form.setInputControls([label, p_level]);
var p_cutoff = p_level.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
for (var row = 0; row < table.numberRows; row++) {
for (var col = 0; col < table.numberColumns; col++) {
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;
}
}
}
table.set(lower_statistic_to_replace, lower_values);
table.set(upper_statistic_to_replace, upper_values);
form.setTranslation("Index", "Upper Confidence Interval");
form.setTranslation("Missing n", "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. 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
- 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.
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