Set Conditional Coloring on Data Bars in Pivot table

Follow

Lee

October 12, 2015 19:25

Introduction

The following article describes the steps needed to set conditional colors in the bars in pivot table.

For example red bars for negative values and green bars for positive.

Implementation Steps

1. Create a pivot chart with at least 1 value

2. Enable data bars for 1 or more values

 

1. Edit the widget’s script, and paste in the below JavaScript code

widget.on('ready', function(sender, ev){

 //get an array of bar elements
 var AllCells = $('.databar');
 
 //find how many bars exist in total including invisible
 var L = AllCells.length;
 
 //init variables
 var MyCell;
 var MyBar;
 var MyCellVal;
 
 //loop through each bar
 for (i=0; i<L; i++) 
 { 
 //look at a specific bar
 MyCell = AllCells[i]; 
 
 //get the value
 MyCellVal =$(MyCell).attr('val'); 
 MyBar = $(MyCell).find('.bar');
 
 switch(true) {
 case (MyCellVal<10000):
 $(MyBar).css('background-color','#01DF3A');
 break;
 case (MyCellVal==10000):
 $(MyBar).css('background-color','#FFBF00');
 break;
 case (MyCellVal>10000):
 $(MyBar).css('background-color','#FE642E');
 break;
 default:
 break; 
 } 
 } 
})

 

1. Edit the desired numeric ranges in the script, and HTML color codes

2. Save the script, refresh the widget and click apply

5

• 

  Rachel Zilberman November 08, 2016 16:34

  Hi,  I am having problem keeping data bars in a pivot where the colors are calculated in a script, maintain the  colors when the dashboard is refreshed.  At times, colors revert to the default blue color, and at times all bars are displayed as the first color appearing in the script.  It almost seems like at times either part or all of the widget script is not executed.  Any ideas?

  Thanks, Rachel

  0

  Comment actions Permalink

Please sign in to leave a comment.