This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Cookie Policy. Click Preferences to customize your cookie settings.
Introduction
This article describes how to dynamically color the Data Bars within a pivot table based on the cell values.
Presented here are two methods:
- Use stock gradients to provide different colors based on cell values
- Define RGB shade magnitudes based on cell values
Examples
Method 1
Method 2
Implementation Steps
Method 1
Utilizing D3.JS Sequential and Diverging Color Scales
This method uses D3 version 5's interpolate methods to select a color based on the bar width.
The "d3.interpolate<color code>" method accepts a float value between 0 and 1 and maps the number to a color within a selected gradient.
A complete list of gradients can be found here under Diverging and Sequential:
Steps
- Navigate to Advanced Widget View
- Copy the snippet below into the Widget custom JavaScript editor to see the results above.
- Save the code
- Refresh the Widget Advanced Editor Page
- Apply Changes
Editing Lightness and Darkness of the Gradient
In this snippet example the range highlighted in green refers to the entire range of the gradient, as 0 represents the start of the gradient, and 1 the end. However often times, you will not want the minimum value represented by 0, because it is too light. The solution is to replace 0 with a slightly higher value, such as 0.2, until you are satisfied with the shade.
Editing Color of the Gradient
In this snippet example the method highlighted in yellow controls the color. To change which color gradient is used, navigate Here and select a d3.interpolate method of a color range you like. Replace the gradient for positive values, and negative values.
If you choose a diverging gradient method, be mindful that you will also need to create two scales, one from 0-.5, and .5 to 1 for negative and positive values respectively
Paste This Snippet into Widget JavaScript Editor
widget.on('ready', function(sender, ev) {
console.log('starting custom code');
// Update D3 to version 5 to allow use of interpolation
$.getScript('https://d3js.org/d3.v5.min.js', function(data, textStatus) {
return true
});
// Convert percentage range to 0 to 1 or any range in between.
myScale = d3.scaleLinear().domain([0, 100]).range([0, 1]);
/**********************************************
Color bars red or green for
positive/negative values.
**********************************************/
//get an array of bar elements
var MyBars = $('.bar', element);
//find how many bars are visible
var L = MyBars.length;
//init variables
var MyBarSign;
var MyBar;
//loop through each bar
for (i = 0; i < L; i++) {
//gets bar width and converts number to a float value
var PercentStringWidth = MyBars[i].style.width;
var IntegerStringWidth = PercentStringWidth.slice(0, -1);
var FloatWidth = parseFloat(IntegerStringWidth);
//apply scale function to the bar width
var ScaledWidth = myScale(FloatWidth);
//look at a specific bar
MyBar = MyBars[i];
//get the class name
MyBarSign = MyBar.parentNode.className.substring(0, 3);
//if the class name starts w/ 'pos', color it using the green gradient. Otherwise color it using the red gradient.
if (MyBarSign == 'pos') {
MyBar.style.backgroundColor = d3.interpolateYlGn(ScaledWidth);
} else {
MyBar.style.backgroundColor = d3.interpolateYlOrRd(ScaledWidth);
}
}
});
Method 2
Creating Self-Defined RGB Color Formulas
This method takes advantage of D3.JS's scaleLinear domain and range functions. The method converts a percentage to a scale that the RGB color values are based on - 0 to 255. For example, 50% is converted to 127.5, or half brightness of a red, green, or blue value. The background color is calculated by converting the width value to a RGB value.
Steps
- Navigate to Advanced Widget View
- Copy the snippet below into the Widget custom JavaScript editor to see the results above.
- Save the code
- Refresh the Widget Advanced Editor Page
- Apply Changes
Editing Lightness and Darkness and Color of the Gradient
In this snippet we are using the RGB values 0 - 255. High values represent lighter tones and lower values darker tones. You can define a function for how the color changes depending on how the width changes, as seen in the yellow highighted text. When using division remember to wrap the function with ParseInt as RGB values cannot be floats.
To select RGB colors you can Google "RGBcolor picker" and use the color picker widget, or use this W3schools page.
Paste This Snippet into Widget JavaScript Editor
widget.on('ready', function(sender, ev) {
console.log('starting custom code');
myScale = d3.scale.linear().domain([0, 100]).range([0, 255]);
/**********************************************
Color bars red or green for
positive/negative values.
**********************************************/
//get an array of bar elements
var MyBars = $('.bar', element);
//find how many bars are visible
var L = MyBars.length;
//init variables
var MyBarSign;
var MyBar;
//console.log(MyBars);
//loop through each bar
for (i = 0; i < L; i++) {
//gets bar width
var PercentStringWidth = MyBars[i].style.width;
var IntegerStringWidth = PercentStringWidth.slice(0, -1);
var FloatWidth = parseFloat(IntegerStringWidth);
//Convert to a 0 - 255 scale
var ScaledWidth = parseInt(myScale(FloatWidth));
//look at a specific bar
MyBar = MyBars[i];
//get the class name
MyBarSign = MyBar.parentNode.className.substring(0, 3);
//if the class name starts w/ 'pos', color it green. Otherwise color it red
if (MyBarSign == 'pos') {
MyBar.style.backgroundColor = 'rgb(' + parseInt(ScaledWidth / 3) + ',' + ScaledWidth + ', ' + 200 + ')';
} else {
MyBar.style.backgroundColor = 'rgb(' + ScaledWidth + ',' + parseInt(ScaledWidth / 3) + ', ' + 200 + ')';
}
}
});
