How to highlight top X values in Pivot tables using custom script [Linux]

Step-by-step Guide

1. Navigate to your preferred Pivot Table widget and open the script editing screen.

2. Next, enter the following script:

const targetCols = [0,3,6]; // Targeted columns for analysis
const columnValues = {};
const highestValues = {};
let initialized = false;
const amountOfTopValues = 4;

// Pass 1: Gather numeric values for target columns
widget.transformPivot({}, (meta, cellData) => {
  // CLEAR columnValues before starting to accumulate values
  if (meta.rowIndex === 0 && meta.colIndex === 0) {
    targetCols.forEach(colIdx => columnValues[colIdx] = []);
  }
  if (
    targetCols.includes(meta.colIndex) &&
    meta.rowIndex > 0 &&
    !meta.type.includes('grandtotal') &&
    !meta.type.includes('subtotal')
  ) {
    if (!columnValues[meta.colIndex]) columnValues[meta.colIndex] = [];
    const numericVal = parseFloat(cellData.content.toString().replace(/,/g,""));
    if (!isNaN(numericVal)) {
      columnValues[meta.colIndex].push({
        num: numericVal,
        pos: meta.rowIndex
      });
    }
  }
});


// Pass 2: Highlight the two highest per column
widget.transformPivot({}, (meta, cellData) => {
  if (targetCols.includes(meta.colIndex) && highestValues[meta.colIndex]) {
    const currentVal = parseFloat(cellData.content.toString().replace(/,/g,""));
    const isHigh = highestValues[meta.colIndex].some(entry =>
      entry.num === currentVal && entry.pos === meta.rowIndex
    );
    if (isHigh) {
      cellData.style = cellData.style || {};
      cellData.style.background = 'LightSalmon';
      cellData.style.fontWeight = 'bold';
    }
  }
});



function calculateTopValuesAndRefresh() {
  targetCols.forEach(colIdx => {
    const entries = columnValues[colIdx] || [];
    const sorted = entries.slice().sort((x, y) => y.num - x.num);
    highestValues[colIdx] = sorted.slice(0, amountOfTopValues);
  });

  if (!initialized) {
    initialized = true;
    setTimeout(function() {
    	widget.refresh();
	  },4000);
    }
}


// Pass 3: Define button to highlight highest values and refresh the widget
widget.on("ready", () => {
 	calculateTopValuesAndRefresh();
	
	let widgetElement = document.querySelector('[widgetid="' + widget.oid + '"]');
  	if (widgetElement) {
    let widgetContainer = widgetElement.querySelector('.widget-body');
    if (widgetContainer) {
      // Don’t add the button multiple times
      if (!widgetContainer.querySelector('.highlight-top-btn')) {
        const button = document.createElement('button');
        button.textContent = "Highlight Top Values";
        button.className = "highlight-top-btn";
		button.style.display = "block";
		button.style.margin = "5px 0 0 10px";
		button.style.background = "#94F5F0";
		button.style.border = "none";
		button.style.borderRadius = "4px";
		button.style.visibility = "visible";
		button.style.cursor = "pointer";
		  
        button.onclick = function() {
			
  			calculateTopValuesAndRefresh();
			widget.refresh();
        };
        widgetContainer.insertBefore(button, widgetContainer.firstChild);
      }
	}
  }
});

3. Please visit your dashboard and look at your widget. You will see a display similar to the following:

4. The highest four values, or your selected amount, will be highlighted in each column specified in the target columns.

5. If you modify the order or make changes to the rows in the widget, the highlighted values may not update automatically. To refresh the highlights, simply click the blue "Highlight Top Values" button whenever needed.

References/Related Content

The Pivot 2.0 API documentation can be accessed here:

https://developer.sisense.com/guides/customJs/jsApiRef/widgetClass/pivot2.html#transformpivot

Conclusion

This code allows you to customize your Pivot Table widgets to consistently highlight the top values of your chosen columns. If your data changes, you can easily use the "Highlight Top Values" feature to update the highlights at any time.

Disclaimer: This post outlines a potential custom workaround for a specific use case or provides instructions regarding a specific task. The solution may not work in all scenarios or Sisense versions, so we strongly recommend testing it in your environment before deployment. If you need further assistance with this, please let us know.