Traffic lighting / conditional formatting - how to change color of text dynamically based on value

Within a tabular visual you sometimes want to highlight certain values. For example, you want to color code specific cells yellow if they are above a threshold.

image

Below is custom style that you can use. Follow the same steps outlined here on how to import the custom style into Arcadia and apply it to your own tabular visual.

Custom Style

return function() {

var f = function() {};

f.version = "1";

f.settings = function() {
    return [
        {
            id: 'Column name',
            defaultValue: 'Scan Exempt Reason'
        },
        {
            id: 'Condition',
            displayName: 'Condition type (>, >=, = , <, <=, between)',
            defaultValue: '>'
        },
        {
            id: 'Min Value',
            displayName: 'Enter a primary threshold',
            defaultValue: 1.5
        },
        {
            id: 'Max Value',
            displayName: 'Enter a secondary threshold',
            defaultValue: 1.5
        },
        {
            id: 'Color',
            defaultValue: '#ffe28f'
        }
    ];
};

f.afterDraw = function() {
    
    //Arcadia API to get result set and use to manipulate visual
    
    var colname = arcapi.getSetting('Column name');
    var threshold1 = arcapi.getSetting('Min Value');
    var threshold2 = arcapi.getSetting('Max Value');
    var c_color = arcapi.getSetting('Color');
    var condition = arcapi.getSetting('Condition');
    
    var colIndex = arcapi.dataResult().columnNameToIndex(colname);
    if (colIndex === undefined) {
        console.log('Unknown columnForLabel for the table-bar extension');
        return;
    }
    
    var d = arcapi.dataResult();
    var rows = d.rows();
    if (rows.length === 0) {
        return;
    }
    
    console.log(threshold1);
    console.log(threshold2);
    console.log(condition);
    
    function heatColm(colIndex) {
        var heat = d3.selectAll("#" + arcapi.chartId() + " .dataTables_scrollBody tbody tr :nth-child(" + (colIndex+1) +")");
        
        function getColor(rowIndex, colIndex) {
            var m = +rows[rowIndex][colIndex];
            console.log(m);
            switch(condition){
                case '>': 
                    if (m > threshold1)
                        return c_color;
                        break;
                case '>=': 
                    if (m >= threshold1)
                        return c_color;
                        break;
                case '=': 
                    if (m == threshold1)
                        return c_color;
                        break;
                case '<': 
                    if (m < threshold1)
                        return c_color;
                        break;
                case '<=': 
                    if (m <= threshold1)
                        return c_color;
                        break;
                case 'between':
                    if (m >= threshold1 && m <= threshold2)
                        return c_color;
                        break;
                default:
                    return undefined;
            }
        }

        var colorsToApply = [];
        heat.filter(function(d, i) {
                var c = getColor(i, colIndex);
                if (c === undefined) {
                    return false;
                }
                colorsToApply.push(c_color);
                return true;
            })
            .style('background-color', function(d, i) {
                return colorsToApply[i];
            })
    }
    
    heatColm(colIndex);

};

return f;
}();
1 Like

Thank you very much. This is very useful.
I have one extended problem. If we want to highlight field with color based on other field which we don’t want to show value the other field value e.g. Only show Node value with color based on property value below

Node Property
John 1
Mary 2
Jane 2
Ted 0
Elisa 2
Mark 2
Patty 3

image

Can this be done without custom style?
Thank you very much.

@Stima_Livingpresent

You should include the column in the visual shelf. But then through the custom style (JS) you hide the column.

Here you see the string column highlighted based on the sum of sales:

image

Then you can hide that column:

image

To hide the column you would do something like this:

        d3.selectAll("#" + arcapi.chartId() + " .dataTables_scrollBody tbody tr :nth-child(" + (colIndex+1) +")")
            .style ("display","none");
        d3.selectAll("#" + arcapi.chartId() + " .dataTables_scrollBody thead tr :nth-child(" + (colIndex+1) +")")
            .style ("display","none");
            
        d3.selectAll("#" + arcapi.chartId() + " .dataTables_scrollHead thead tr :nth-child(" + (colIndex+1) +")")
            .style ("display","none");

        d3.selectAll("#" + arcapi.chartId() + "  .dataTables_scrollHeadInner")
            .style("width","100%");   
            
        var $chart = $('#' + arcapi.chartId() + ' table[id^=data-table]');
        var dataTable = $chart.data('data-table');
        dataTable.columns.adjust();

The full code below.

Custom Style Code

return function() {

var f = function() {};

f.version = "1";

f.settings = function() {
    return [
        {
            id: 'Column name',
            defaultValue: 'Scan Exempt Reason'
        },
        {
            id: 'Condition',
            displayName: 'Condition type (>, >=, = , <, <=, between)',
            defaultValue: '>'
        },
        {
            id: 'Min Value',
            displayName: 'Enter a primary threshold',
            defaultValue: 1.5
        },
        {
            id: 'Max Value',
            displayName: 'Enter a secondary threshold',
            defaultValue: 1.5
        },
        {
            id: 'Color',
            defaultValue: '#ffe28f'
        }
    ];
};

f.afterDraw = function() {
    
    //Arcadia API to get result set and use to manipulate visual
    
    var colname = arcapi.getSetting('Column name');
    var threshold1 = arcapi.getSetting('Min Value');
    var threshold2 = arcapi.getSetting('Max Value');
    var c_color = arcapi.getSetting('Color');
    var condition = arcapi.getSetting('Condition');
    
    var colIndex = arcapi.dataResult().columnNameToIndex(colname);
    if (colIndex === undefined) {
        console.log('Unknown columnForLabel for the table-bar extension');
        return;
    }
    
    var d = arcapi.dataResult();
    var rows = d.rows();
    if (rows.length === 0) {
        return;
    }
    
    console.log(threshold1);
    console.log(threshold2);
    console.log(condition);
    
    function heatColm(colIndex) {
        var heat = d3.selectAll("#" + arcapi.chartId() + " .dataTables_scrollBody tbody tr :nth-child(" + (colIndex+1) +")");
        var heatcol = d3.selectAll("#" + arcapi.chartId() + " .dataTables_scrollBody tbody tr :nth-child(" + (colIndex) +")");
        
        function getColor(rowIndex, colIndex) {
            var m = +rows[rowIndex][colIndex];
            console.log(m);
            switch(condition){
                case '>': 
                    if (m > threshold1)
                        return c_color;
                        break;
                case '>=': 
                    if (m >= threshold1)
                        return c_color;
                        break;
                case '=': 
                    if (m == threshold1)
                        return c_color;
                        break;
                case '<': 
                    if (m < threshold1)
                        return c_color;
                        break;
                case '<=': 
                    if (m <= threshold1)
                        return c_color;
                        break;
                case 'between':
                    if (m >= threshold1 && m <= threshold2)
                        return c_color;
                        break;
                default:
                    return undefined;
            }
        }

        var colorsToApply = [];
        heatcol.filter(function(d, i) {
                var c = getColor(i, colIndex);
                if (c === undefined) {
                    return false;
                }
                colorsToApply.push(c_color);
                return true;
            })
            .style('background-color', function(d, i) {
                return colorsToApply[i];
            });
            
            
        d3.selectAll("#" + arcapi.chartId() + " .dataTables_scrollBody tbody tr :nth-child(" + (colIndex+1) +")")
            .style ("display","none");
        d3.selectAll("#" + arcapi.chartId() + " .dataTables_scrollBody thead tr :nth-child(" + (colIndex+1) +")")
            .style ("display","none");
            
        d3.selectAll("#" + arcapi.chartId() + " .dataTables_scrollHead thead tr :nth-child(" + (colIndex+1) +")")
            .style ("display","none");

        d3.selectAll("#" + arcapi.chartId() + "  .dataTables_scrollHeadInner")
            .style("width","100%");   
            
        var $chart = $('#' + arcapi.chartId() + ' table[id^=data-table]');
        var dataTable = $chart.data('data-table');
        dataTable.columns.adjust();
    }
    
    
    heatColm(colIndex);

};

return f;
}();
1 Like

Shaun, Thank you very much. It works perfectly.

@Shaun,
If in case cross table. Is there any way to overwrite rule for highlight colours?
Thank you.

@Stima_Livingpresent you can use custom colors as explained here:

http://documentation.arcadiadata.com/4.4.0.0/#pages/topics/custom-colors-gradient-val.html

By using the gradient option, you have the ability to control the thresholds for the color range. Note that this color setting applies to the entire cross-tab. You cannot have varying set of custom colors across column or rows.

In the below example we use the custom color to force any value above ~100K to be red for example.

2 Likes

@shaun Thank you for your feedback. Its working :smile:

@shaun
We have tried to compare string (e.g. Yes , No) in javascript using locale but it is not working, always goes to else condition.
Not sure it is working in Arcadia.
Thank you very much.