Change colors on refresh

[dave-killough]

I'm hoping to change the color scale based on the selected aggregator with the code below. My percentages, amounts, and counts all need different ranges/colors. I'm setting the option in the onRefresh, but it appears that it's after the colors are already applied, and I don't see them change until I refresh again. I looked around for another event where I could change the configuration earlier, but I don't see any others. My next thought is to add an event handler on that aggregator field to see if a config change can be injected there.

Is there hope? Thanks, Dave

onRefresh: function (config) {

    if (config.aggregatorName == 'Count') {
        config.rendererOptions.heatmap.colorScaleGenerator = function(values) {
            return Plotly.d3.scale.linear()
                    .domain([0, 1_000])
                    .range(["#FFF", "#0F0"])
        } 
    }
    if (config.aggregatorName == 'Integer Sum') {
        config.rendererOptions.heatmap.colorScaleGenerator = function(values) {
            return Plotly.d3.scale.linear()
                    .domain([0, 1_000_000])
                    .range(["#FFF", "#00F"])
        } 
    }                            
}

[dave-killough]

Well, this seems to work the first time, but then the call to pivotUI appears to overwrite my change handler. I just want the heatmap colors to adapt to the range of my selected aggregator so I can switch among counts, amounts, and percentages. Maybe another event could be added before the rendering is applied. Is there another way to dynamically change heatmap colors based on aggregator selection?

$('#output .pvtAggregator').on('change',function() {
    var config = $('#output').data("pivotUIOptions")
    config.aggregatorName = this.value;
    if (this.value == 'ASSET_AMT') {
        config.rendererOptions.heatmap.colorScaleGenerator = function(values) {
            return Plotly.d3.scaleLinear().domain([0, 500_000]).range(["#FFF", "#00F"])
        } 
    }
    if (this.value == 'CONTRIB_GROWTH') {
        config.rendererOptions.heatmap.colorScaleGenerator = function(values) {
            return Plotly.d3.scaleLinear().domain([-100, 0, 100]).range(["red", "white", "green"])
        } 
    }
    $('#output').pivotUI(output_data, config, true).show()
});

[dave-killough]

Here's my pivot:

DataPivot_1023.html

You can see the same heatmap color ranges do not work across averages, amounts, and counts. Each one should have its own range and different colors would be very cool. The temperature example looks very nice, but you only need one scale for that.

[dave-killough]

Ok, I got this working by attaching my handler higher up and delegating to the disappearing dropdown.

$('body').on('change','#output .pvtAggregator',function() {

[dave-killough]

I thought this was good, but overwriting the UI to fix up the parameters is awkwardly slow. Is there another way of updating the colorScaleGenerator before the chosen render that doesn't involve the wait? The first wait to load the UI was acceptable because it only happened once. The pause on every aggregator change is too long. Thanks so much.

[dave-killough]

Here's the pivot with the overwrite: https://storage.googleapis.com/benevolent-machines/DataPivot_990.html

[dave-killough]

I figured out a better way!

I returned to the onRefresh() callback. I saw a heatmap() function that works as a post-processing step to the renderer. I simply called it again with the updated configuration and the colors switch nicely as I change aggregators. Another enhancement that inspects the range of values currently displayed should ensure a more balanced color output. I might add a pivotData.forEachMatchingRecord() loop at some point to determine the range. And very skewed values might look better with a Log scale. It should all work now and I'm going to get what I need here.

Nicolas - Thanks for making easy things easy and some hard things possible.

onRefresh: function (config) {
    nrecoPivotExt.initFixedHeaders($('#output table.pvtTable'));

    if (config.aggregatorName == 'ORG_COUNT') {
        config.rendererOptions.heatmap.colorScaleGenerator = function(values) {
            return Plotly.d3.scale.linear()
                    .domain([0, 1_800_000]).range(["#FFF", "#0F0"])
        } 
    }
    if (config.aggregatorName == 'ASSET_TOT') {
        config.rendererOptions.heatmap.colorScaleGenerator = function(values) {
            return Plotly.d3.scale.linear()
                    .domain([0, 4_000_000_000_000]).range(["#FFF", "#00F"])
        } 
    } 
    if (config.rendererName == 'Heatmap') {
        $('#output').heatmap('heatmap', config.rendererOptions)
    }
}

[dave-killough]

d3.scale.sqrt() is better balanced for me than d3.scale.log()

[nicolaskruchten]

Wow, that's a really great solution, nice job! Sorry I haven't been able to respond to this to help out but you figured out a neat way forward which I probably would not have.

Nicolas - Thanks for making easy things easy and some hard things possible.

This is high praise, thank you 🙇 and... you're welcome 😄

[jazo2212]

Estimado, necesito una ayuda como fijar el encabezado de mi tabla dinámica para que a través de un scroll solo se desplacen las filas de registros. Este es el código que tengo en mi pivote.js. Además, es posible que en el comboList o comboBox donde se seleccionan los campos se pueda implementar hacer una selección multiple o selección mas de un registros para cargar la tabla dinámica?? La tabla dinámica se carga desde un archivo Excel

Este es el código que tengo actualmente.

// pivote.js
// Función para crear Tabla Dinámica que se adapta a diferentes estructuras de datos
function criarTabelaDinamica(data) {
    // Verificar si los datos son un array de objetos
    if (Array.isArray(data) && data.length > 0 && typeof data[0] === 'object') {
        // Supongamos que el objeto data es una lista de objetos donde cada objeto representa una fila del Excel
        // Aquí se extraen las claves de un objeto para usarlas como nombres de columna
        const columnKeys = Object.keys(data[0]);
          // Función para generar colores aleatorios
          function generateRandomColors(count) {
            const colors = [];
              for (let i = 0; i < count; i++) {
                    const color = '#' + Math.floor(Math.random() * 16777215).toString(16);
                    colors.push(color);
             }
                     return colors;
          }
        // Aquí se configuran las opciones para la tabla dinámica
        const pivotOptions = {
            rows: [columnKeys[0]], // Tomamos la primera clave como fila
            cols: [columnKeys[1]], // Tomamos la segunda clave como columna
            aggregatorName: 'Sum',
            vals: [columnKeys[2]], // Tomamos la tercera clave como valor
            renderers: {
                'Table': $.pivotUtilities.renderers['Table'],
                'Table Barchart': $.pivotUtilities.renderers['Table Barchart'],
                'Heatmap': $.pivotUtilities.renderers['Heatmap'],
                'Row Heatmap': $.pivotUtilities.renderers['Row Heatmap'],
                'Col Heatmap': $.pivotUtilities.renderers['Col Heatmap'],
            }
        };
        // Crear la tabla dinámica
        $('#pivotTable').pivotUI(data, pivotOptions);
    } else {
        console.error('Los datos proporcionados no son válidos para crear la tabla dinámica.');
    }
}
// Función para manejar la carga de un archivo Excel
document.getElementById('fileUpload').addEventListener('change', function(event) {
    const file = event.target.files[0];
    if (file) {
        const reader = new FileReader();
        reader.onload = function(e) {
            const data = new Uint8Array(e.target.result);
            const workbook = XLSX.read(data, { type: 'array' });
            const sheetName = workbook.SheetNames[0]; // Suponiendo que los datos están en la primera hoja
            const jsonData = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName]);
            criarTabelaDinamica(jsonData);
        };
        reader.readAsArrayBuffer(file);
    }
});

Esta es la linea que esta en mi tabla.html

<div id="pivotTable" class="pvtTable"> </div>