Modify Cell Content - Blank Cells with Small Values

From Q
Jump to: navigation, search

This rule allows you to choose a statistic and a value, and any cell with a value below that level will become blank.


Here we have chosen to blank any cell with a Column % value smaller than 10%.


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.


  • 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).


includeWeb('JavaScript Utilities');
includeWeb('Table JavaScript Utility Functions');
var translated_stats = {
    try {
	return table.getTranslation(s);
    }catch(e) {
	return s;

// Set up controls for user input.
form.setHeading('Blanking Cells With Values Below a Threshold:');

var label_stats = form.newLabel('Choose a cell statistic:');
var combobox_stats = form.newComboBox('stats', translated_stats);

var label = form.newLabel('Minimum Value:');
var numeric_up_down  = form.newNumericUpDown('threshold');
form.setInputControls([label_stats,combobox_stats,label, numeric_up_down]);
var selected_stat_translated = combobox_stats.requireValue();
var selected_statistic = stats[translated_stats.indexOf(selected_stat_translated)];
var min_value = numeric_up_down.requireValue();
form.setSummary('Blank cells where ' + selected_stat_translated + ' < ' + min_value);

if (table.availableStatistics.indexOf(selected_statistic) == -1)
    form.ruleNotApplicable(selected_stat_translated + " is not available on this table");

var blank_flag = false; // Keep track of whether any cells have been blanked.
var values = table.get(selected_statistic);// Get statistic values in all the cells
for (var column = 0; column < table.numberColumns; column++) {// loop through the columns
    for (var row = 0; row < table.numberRows; row++) {
        var stat_value = values[row][column];
        // If the cells selected statistics is less than min_value, make each cell blank.
        if (stat_value < min_value) {
            table.blankCell(row, column);
            blank_flag = true;

// Store the extra footnotes we generate for each blank cell.
if (blank_flag) {
    var extra_footers = table.extraFooters; 
    extra_footers.push('Blank cells have ' + selected_stat_translated +
		       ' that is smaller than ' + min_value + '.');
    table.extraFooters = extra_footers;

See also