Modify Cell Content - Highlight Cells Above and Below NET

From Q
Jump to navigation Jump to search

This rule highlights cells with values a user-specified percentage above and below the NET (default is 20%). The highlighting colors are also selectable.

Example

HighlightHighLowVsNETRule.PNG

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

table.requireNumericTable();

// Set up controls for user input.
form.setHeading('Highlighting Cells Above and Below NET:');
var label_statistic = form.newLabel('Statistic to use:');
var translated_statistics = table.statistics.map(function(s) {
    try {
	return table.getTranslation(s);
    }catch(e) {
	return s;
    }
});
var combo_box = form.newComboBox('statistic', translated_statistics);
combo_box.setDefault(translated_statistics[0]);
var label_threshold = form.newLabel('Threshold (%):');
var numeric_up_down  = form.newNumericUpDown('threshold');
numeric_up_down.setDefault(20);
numeric_up_down.setIncrement(1);
form.setInputControls([label_statistic, combo_box, label_threshold, numeric_up_down]);
var color_picker_1 = form.newColorPicker('color1');
var color_picker_2 = form.newColorPicker('color2');
color_picker_1.setDefault('PaleGreen');
color_picker_2.setDefault('Pink');
form.setOutputControls([color_picker_1, color_picker_2]);
form.setOutputColors([color_picker_1.getValue(), color_picker_2.getValue()]);
var statistic_translated = combo_box.getValue();
var statistic = table.statistics[translated_statistics.indexOf(statistic_translated)];
var percent = numeric_up_down.getValue();
form.setSummary('Highlight cells with ' + statistic_translated +
		' that is ' + percent + '% above and below NET');

// Get the statistic's values.
var values = table.get(statistic);

if (!table.columnLabels)
    form.ruleNotApplicable('this table does not have a suitable NET');

// Work out the orientation of the table.
var by_columns;
var net_row;
var net_column;
var net_rows = table.netRows;
if (net_rows.length > 0 && values[net_rows[0]][0] != 100.0) {
    net_row = net_rows[0];
    by_columns = true;
} else {
    var net_columns = table.netColumns;
    if (net_columns.length > 0 && values[0][net_columns[0]] != 100.0) {
        net_column = net_columns[0];
        by_columns = false;
    } else
        form.ruleNotApplicable('this table does not have a suitable NET');
}

// Get the array of cell colors (currently blank, or white).
var cell_colors = table.cellColors;
var upper_threshold = 1 + percent / 100;
var lower_threshold = 1 - percent / 100;

for (var row = 0; row < table.numberRows; row++)
    for (var column = 0; column < table.numberColumns; column++) {
        var value = values[row][column];
        var NET_value = by_columns ? values[net_row][column] : values[row][net_column];
        var ratio = value / NET_value;
        if (ratio >= upper_threshold)
            cell_colors[row][column] = color_picker_1.getValue();
        else if (ratio <= lower_threshold)
            cell_colors[row][column] = color_picker_2.getValue();
    }

// Set the array of cell colors we just modified.
table.cellColors = cell_colors;

See also

Retrieved from ‘https://wiki2.q-researchsoftware.com/index.php?title=Modify_Cell_Content_-_Highlight_Cells_Above_and_Below_NET&oldid=53350’