Highlight 1st column in Pivot using values from another column

[DustinSmith]

Hello,

I'm using Sisense Linux and I have a pivot table that I'm using to stoplight certain locations with green, yellow, or red.  My 1st column is location(like NYC), my 2nd column is text status(GREEN), and my 3rd column is number status(3).  I would like to use either the 2nd or 3rd column to determine what color to highlight the 1st column.

What I have:

What I'd like:

I know pivots are highly customizable in Linux via widget scripts, but I haven't been able to get it working.

Thanks

[harikm007 · Accepted solution]

@DustinSmith 

Please check if this script works for you:

widget.on('domready', function(se, ev){
	
	ColorValueColumnIndex = '2' //Column index based on which color to choose
	ApplycolorToColumnIndex = '0' //Index of the column in which the color should be applied
	
	if (prism.activeWidget == null)
		parentElement = $('[widgetid="' + widget.oid + '"] pivot2');
	else
		parentElement = $('pivot2')

	$(parentElement).find('.pivot-scroller .pivot-scroller__view .table-grid__table tbody tr').each(function(index){
		
		if(index == 0)
			return
			
		colorValue = $(this).find(`.table-grid__cell--col-${ColorValueColumnIndex}`).text().trim()

		if(colorValue == '4')
			color = '#d0f7da'
		else if(colorValue == '12')
			color = '#d0e4f7'
		else if(colorValue == '14')
			color = '#f7d0f3'
		else if(colorValue == '6')
			color = '#f7d0d6'
		else if(colorValue == '8')
			color = '#f7efd0'
		else
			color = '#FFFFFF'

		$(parentElement).find(`.multi-grid .table-grid table tbody tr.table-grid__row-${index} .table-grid__cell--col-${ApplycolorToColumnIndex}`).css('background-color', color)
	}) 
})

-Hari

[DustinSmith]

Excellent! Thank you so much! This is what it looks like now without any manual intervention:

I was working with Anton from support and we got close.  That solution was highlighting all columns except the first one.  I'll post that script below(while obscuring the specific locations) in case anyone has a similar use case:

Script:

const target = {
rows: [
{
dim: "[LocationStoplight.status]",
members: ["GREEN"],
},
],
};

function setCellBackground(metadata, cell) {
cell.style = cell.style || {};
cell.style.backgroundColor = 'green';
}

widget.transformPivot(target, setCellBackground);

const target2 = {
rows: [
{
dim: "[LocationStoplight.status]",
members: ["RED"],
},
],
};
function setCellBackground2(metadata, cell) {
cell.style = cell.style || {};
cell.style.backgroundColor = 'red';
}

widget.transformPivot(target2, setCellBackground2);

const target3 = {
rows: [
{
dim: "[LocationStoplight.status]",
members: ["YELLOW"],
},
],
};
function setCellBackground3(metadata, cell) {
cell.style = cell.style || {};
cell.style.backgroundColor = 'yellow';
}

widget.transformPivot(target3, setCellBackground3);