UPCOMING WEBINAR: The complete guide to analyzing NPS This article describes how to go from a standard table...
...to a table that hides rows under a specific value:
Requirements
- A summary table or crosstab.
- In this example, we will hide any percentages below 40, but you can choose a different statistic and threshold, if you wish.
- If using this rule on a crosstab, you will have the choice of hiding any row where all the values are below this threshold or only the values in a specific column.
Method
1. Select your table.
2. Go to Data > Rules in the object inspector.
3. Click the Plus (+) button.
4. Click New custom rule (write your own JavaScript) > Edit JavaScript.
5. Paste the below into the dialog:
// Include JavaScript library
includeWeb('JavaScript Array Functions');
form.setHeading("Hide rows below specified value");
form.setSummary("Hide rows below specified value");
// Add user input to select statistic
let available_stats = table.availableStatistics;
// Remove inappropriate statistics
let valid_statistics = available_stats.filter(function (statistic) {
return statistic !== 'Column Comparisons' && statistic !== 'Column Names' &&
statistic !== 'Columns Compared';
});
// Get translated names if changed
let valid_stats_translated = valid_statistics.map(function(s) {
try {
return table.getTranslation(s);
} catch(e) {
return s;
}
});
// Set statistic for threshold
let stat_label = form.newLabel("Statistic to use:");
let stat_box = form.newComboBox("stat",valid_stats_translated);
stat_box.setDefault(valid_stats_translated[0]);
stat_box.lineBreakAfter = true;
// Set threshold
let threshold_label = form.newLabel("Threshold:");
let threshold_box = form.newNumericUpDown("thresh");
threshold_box.setDefault(10);
threshold_box.setMaximum(9999999);
threshold_box.lineBreakAfter = true;
// Set column criteria
let column_list = [];
let control_array = [stat_label, stat_box, threshold_label, threshold_box];
let column_selection_label, column_selection_box, column_choice_box, column_choice_label;
let col_select = false;
if (table.numberColumns > 1) {
column_selection_label = form.newLabel('Selection criteria:');
column_selection_box = form.newComboBox('csm', ['Any column', 'Choose a column']);
column_selection_box.setDefault('Any column');
column_selection_box.lineBreakAfter = true;
control_array.push(column_selection_label);
control_array.push(column_selection_box);
// Set dynamic field for choosing a specific column to base the calculation on
if (column_selection_box.getValue() == 'Choose a column' && table.columnLabels != null) {
column_list = enumerateDuplicatesInStringArray(table.columnLabels, '(', ')');
column_choice_label = form.newLabel('Column:')
column_choice_box = form.newComboBox('ccm', column_list);
column_choice_box.setDefault(column_list[0]);
column_choice_box.lineBreakAfter = true;
control_array.push(column_choice_label);
control_array.push(column_choice_box);
col_select = true;
}
}
// Combine all user input fields
form.setInputControls(control_array);
// Returns true if row for specified column is below threshold
function arrayRowsBelowMin(array,threshold) {
let non_below_row = array.map(function(x) { return x <= threshold; } );
return non_below_row[0];
}
// Returns true if all elements of the array are below threshold
function arrayElementsBelowMin(array,threshold) {
let non_below = array.filter(function(x) { return x >= threshold; } );
return non_below.length == 0;
}
// Returns true if all elements of the array are NaN
function arrayElementsAllNaN(array) {
let not_nan = array.filter(function(x) { return !isNaN(x); } );
return not_nan.length == 0;
}
// Obtain the statistics to check
let target_statistic = stat_box.getValue();
let stat = valid_statistics[valid_stats_translated.indexOf(target_statistic)];
let values = table.get(stat);
// Check for rows with minimum threshold
let min_rows = [];
let threshold = threshold_box.getValue();
let col_index;
// Get data for selected column (if chosen)
if(col_select) {
let col_selection = column_choice_box.getValue();
col_index = table.columnIndex(col_selection);
}
// Perform validation and find rows to hide
for (let j = 0; j < table.numberRows; j++) {
let current_row_values = col_select ? values[j][col_index] : values[j];
let condition = col_select ? arrayRowsBelowMin([current_row_values],threshold) : arrayElementsBelowMin(current_row_values,threshold) || arrayElementsAllNaN(current_row_values);
if (condition)
min_rows.push(j);
}
// Remove specified rows
if (min_rows.length > 0)
for (let j = min_rows.length - 1; j > -1; j--) {
table.deleteRow(min_rows[j]);
if (typeof right_table !== 'undefined') {
if (right_table.numberRows == 1)
right_table.deleteColumn(min_rows[j]);
else
right_table.deleteRow(min_rows[j]);
}
}
In the code:
- We first find only the appropriate statistics to show to the user and display the translated name if the statistic name has been modified.
- Next, we set up the input user form to allow the statistic and threshold parameters to be set, as well as whether we base the condition on all values in a row being below the threshold or values in a specific column only.
- We then build an array of all the values across the columns in each row and check whether they are below the specified threshold.
- Finally, we use the
deleteRowfunction to remove any row where this condition is true ordeleteColumnwhen dealing with a grid question where the table dimensions are reversed. - Note, as significance testing is conducted prior to the application of the Table JavaScript, the significance highlighting from the cells in the original table will be shown.
6. Press the Play button and click OK.
7. Select the Statistic to use and set the Threshold for working out which rows to hide and when to hide them:
[If a crosstab]: By default, Selection criteria will be set to Any column. This means the rule will hide any row where all the values are below the specified threshold. To instead hide rows based on the values in a specific column, change this to Choose a column and select the appropriate column from the Column drop-down:
8. Click OK.