Dynamic Pivot Table Heat Map

[intapiuser]

Introduction

This article describes how to dynamically color cells based on values included in the entire view.

The visualization method assists in seeing trends across the entire pivot table. Retention, Cohort, and usage analyses benefit the most from this type of visualization.

Limitations

The formatting will not be applied to pdf exports

Examples

User Retention

Aggregate User Retention

Cohort Analysis

Implementation 

STEPS

1. Navigate to Advanced Widget View
2. Copy the snippet below into the Widget custom JavaScript editor to see the results above.
3. Save the code
4. Refresh the Widget Advanced Editor Page
5. Apply Changes

EDITING COLOR OF THE GRADIENT

To change which color gradient is used, navigate Here and select a d3.interpolate method of a color range you like. Replace the gradient assigned to the variable colorTheme at the top of the script. Make sure to preserve the single quotes.

 Options available on the D3 scale documentation page

EDITING LIGHTNESS AND DARKNESS OF THE GRADIENT

To control the range of the color gradient edit the leftColorIntensity and rightColorIntensity variables at the top of the script. In the example provided the leftColorIntensity is set to 0.2 and returns an orange color, while the rightColorIntensity is set to 0.7 and returns a light green. Maximum values of the gradients are 0 and 1 for the left and right sides respectively.

PASTE THIS SNIPPET INTO WIDGET JAVASCRIPT EDITOR

Windows Version

// select color theme from --> https://github.com/d3/d3-scale-chromatic
var colorTheme = 'interpolateRdYlBu';
var leftColorIntensity =0.7;
var rightColorIntensity = 0.9;

widget.on("ready", function(w, args){
 
 $.getScript('https://d3js.org/d3.v5.min.js', function(data, textStatus) {
 var tags = $('.p-value.p-first-data-col', element);
 var maxValue = 0.0;
 var minValue = 0.0;

if (parseFloat(tags[0].textContent)){
 minValue = parseFloat(tags[0].textContent)
 }

for(var i=0; i<tags.length; i++) {
 
 var tagText = $(tags[i]).attr('val');
 
 if(parseFloat(tagText) && parseFloat(tagText) > maxValue) {
 maxValue = parseFloat(tagText);
 }

if(parseFloat(tagText) && parseFloat(tagText) < minValue) {
 minValue = parseFloat(tagText);
 }
 }

myScale = d3.scaleLinear()
 .domain([minValue, maxValue])
 .range([leftColorIntensity, rightColorIntensity]);
 
 for(var i=0; i<tags.length; i++) {
 var tagText = $(tags[i]).attr('val');
 var colorValue = 0.0;

if (parseFloat(tagText)){
 colorValue = parseFloat(tagText)
 }
 var scaled = myScale(colorValue);
 tags[i].style.backgroundColor = d3[colorTheme](scaled);
 }
 });
});

Linux Version

// select color theme from --> https://github.com/d3/d3-scale-chromatic
var colorTheme = 'interpolateYlGn';
var leftColorIntensity = 0.1;
var rightColorIntensity = 0.9;
widget.on("ready", function (w, args) {
    $.getScript('https://d3js.org/d3.v5.min.js', function (data, textStatus) {
        var tags = $('.table-grid__cell--col-parent-sibling-last', element);
        var maxValue = 0.0;
        var minValue = 0.0;
        //debugger;
        for (var i = 0; i < tags.length; i++) {
            let tagText = tags[i].textContent;
            let parseVal = parseFloat(tagText);
            if (parseVal && parseVal > maxValue) {
                maxValue = parseVal;
            }
            if (parseVal && parseVal < minValue) {
                minValue = parseVal;
            }
        }
        myScale = d3.scaleLinear()
            .domain([minValue, maxValue])
            .range([leftColorIntensity, rightColorIntensity]);
        for (var i = 0; i < tags.length; i++) {
            let tagText = tags[i].textContent;
            let parseVal = parseFloat(tagText);
            let colorValue = 0.0;
            if (parseVal) {
                colorValue = parseVal;
                let scaled = myScale(colorValue);
                tags[i].style.backgroundColor = d3[colorTheme](scaled);
            }
        }
    });
});  

Code Insight

Utilizing D3.JS Sequential and Diverging Color Scales

This method uses D3 version 5's interpolate methods to select a color based the cell value. Before this method is used, the code converts the minimum and maximum and all values in between found in the pivot table to a range between 0 and 1. This is done because the "d3.interpolate<color code>" method used to select the color only accepts values between 0 and 1.

Red to Green Gradient Scale

A complete list of gradients can be found here under Diverging and Sequential:

https://github.com/d3/d3-scale-chromatic