Knowledge Base Article

Gradient Conditional Formatting In Pivot Tables

Conditional formatting of background colors in pivot tables is one of the best ways to draw attention to numbers, but still provide a clean visual.  The use of data bars creates a lot of visual noise, whereas background colors are subtle and act more like a heatmap.

The code below can be added to any pivot widget to conditionally format cells based on their values from a solid color for the max value down to white for the min value.

In order for this code to work, the pivot table must have the "data bars" feature enabled.  This conditional formatting simply swaps the data bars for background colors.

Here is the code:
var colors = ['77, 197, 255', '226, 112, 255'];
var startColIdx = 2;

widget.on('ready',function(e,ev) {
   for (var i in colors){
      var color = colors[i];
      $(element).find(".databar[fidx=" + (parseInt(i,10)+startColIdx) + "] .bar-container .bar").each(function(x,e){ 
         $(e).css('background-color', 'rgba(0,0,0,0)');
         $(e).closest('.databar').css('background-color','rgba(' + color + ',' + ($(e).width()/$(e).parent().width()) + ')'); 
      });
   }
});
The code has 2 variables
  1. The colors array, these are the RGB colors for the max value for each of the value columns
  2. The startColIdx is the column number that the pivot values start at.  If you have 1 "Rows" item, the startColIdx is 2.  If you have 2 rows, the startColIdx is 3 and so on.
Below is an image showing the result of applying this code to a pivot widget.
Updated 03-02-2023

5 Comments

  • dp_ps's avatar
    dp_ps
    Data Storage

    Hi intapiuser 

    Are there any other pre-requisites to using this code beside enabling the data bars? For some reason, the code isn't working for me. 

  • Hi intapiuser , 

    This code is not working for me. I checked that data bars are on. 
    Also, just in case I tried making the structure of table same as yours but still it didn't work.

    Can you please help me?

    Thanks

  • Hello angupta,

    This post was migrated from a previous platform, I'm not sure if the code works in more recent versions of Sisense. If you like I can get you in touch with a technical resource to work with you, or please post your question in the Help & How-To forum.

  • Sure DRay ,

    I would be helpful if you could.

    I will also post that question in forum.

    Thanks