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

includeWeb('QScript Utility Functions');
includeWeb('Table JavaScript Utility Functions');

excludeRTables();
table.requireNumericTable();

// Set up controls for user input.
form.setHeading('Highlighting Cells Above and Below NET');

let description = form.newLabel('Add color to cells whose value is greater or lower than the value in the NET column');
description.lineBreakAfter = true;

let label_statistic = form.newLabel('Statistic to use:');
let translated_statistics = translateStats(table.statistics);

let combo_box = form.newComboBox('statistic', translated_statistics);
combo_box.setDefault(translated_statistics[0]);
combo_box.lineBreakAfter = true;
let label_threshold = form.newLabel('Threshold (%):');
let numeric_up_down  = form.newNumericUpDown('threshold');
numeric_up_down.setDefault(20);
numeric_up_down.setIncrement(1);
numeric_up_down.lineBreakAfter = true;
let color_picker_1 = form.newColorPicker('color1');
let color_picker_2 = form.newColorPicker('color2');
color_picker_1.setDefault('PaleGreen');
color_picker_1.lineBreakAfter = true;
color_picker_2.setDefault('Pink');
let color_label_1 = form.newLabel('Higher values:');
let color_lable_2 = form.newLabel('Lower values:');

let statistic_translated = combo_box.getValue();
let statistic = table.statistics[translated_statistics.indexOf(statistic_translated)];
let percent = numeric_up_down.getValue();
form.setInputControls([label_statistic, combo_box, label_threshold, numeric_up_down, color_label_1, color_picker_1, color_lable_2, color_picker_2])
form.setOutputColors([color_picker_1.getValue(), color_picker_2.getValue()]);
form.setSummary('Highlight cells with ' + statistic_translated +
		' that is ' + percent + '% above and below NET');

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

if (table.brownQuestion.isBanner && table.netColumns.length == 0) {
    let banner_message = 'the banner does not have an overall NET column. '
        + 'To add an overall NET, ' 
        + (inDisplayr() ? 'select the table and then select "Add overall NET" on the right'
                                                   : 'edit the banner and select "Add overall NET"');
    form.ruleNotApplicable(banner_message);
}

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

// Work out the orientation of the table.
let by_columns;
let net_row;
let net_column;
let 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 {
    let 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).
let cell_colors = table.cellColors;
let upper_threshold = 1 + percent / 100;
let lower_threshold = 1 - percent / 100;

for (let row = 0; row < table.numberRows; row++)
    for (let column = 0; column < table.numberColumns; column++) {
        let value = values[row][column];
        let NET_value = by_columns ? values[net_row][column] : values[row][net_column];
        let 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://wiki.q-researchsoftware.com/index.php?title=Modify_Cell_Content_-_Highlight_Cells_Above_and_Below_NET&oldid=64272’