Replace Values with Text - Pivot Table Widget

[Community_Admin]

Introduction

The following enables to change the value in pivot tables to specific strings, while allowing the use of conditional coloring as normal.

Step 1 - Add The Following Script To Your Pivot's Edit Script

widget.on('ready', function(){
$('td[class*=p-value] div').map(function(i, cell) { 
switch (cell.innerHTML ) {
case "10": cell.innerHTML='BAD';
break;
case "20": cell.innerHTML='OK';
break;
case "30": cell.innerHTML='MEH';
break;
default: cell.innerHTML='GOOD';
}
}) 
})

Step 2 - Modify the values in the script according to your need

You can change the 'case' values to match the original values and the "cell.innerHTML" values to the strings you wish to present.

Note: The value after default will determine what will happen with all values not matching the cases above. If you wish to leave the original values in this case simply enter "cell.innerHTML" instead of "GOOD".

Example:

After script:

You can also achieve similar results with the following script:

fidx is the value you wish to change (starts at 1).

widget.on('ready', () => {
$('td.p-value[val="10"][fidx="1"]').text("BAD")
$('td.p-value[val="20"][fidx="1"]').text("OK")
$('td.p-value[val="30"][fidx="1"]').text("MEH")
$('td.p-value[fidx=1] div').filter(function() {return parseFloat($(this).text().replace("$","").replace(",","")) > 30;}).text("GOOD")
})

[wallingfordce]

Can you expand on this in the context of your example?

> fidx is the value you wish to change (starts at 1).

Does 

[fidx="1"]

Become

[Total Quantity="1"]

And what is the implication of "1" here?

Thanks in advance.

[wallingfordce]

Nevermind, sorted it. It's the "1" that changes (e.g. fidx="4") to indicate which column is impacted by the script.

[Wojtek]

Hi

Are those scripts still working ?

We are on L2022.4.0 . I tried both scripts and couldn't get desired result. I'm pretty sure this worked previously so I am wondering if this stopped working with last upgrade.

I would appreciate if someone could confirm, thanks.

[rvickersevotix]

Yeah would love to know if there is an update as neither of those work.

[Wojtek]

HI all,

Im on L2023.2 and this works for me:

widget.on('ready', function(){
$("div[class*='--col'] div.table-grid__content__inner").map(function(i, cell) { 
switch (cell.innerHTML ) {
case "-1": cell.innerHTML='PASS';
break;
case "-2": cell.innerHTML='WARNING' ;
break;
case "-3": cell.innerHTML='FAIL';
break;
default: cell.innerHTML=cell.innerHTML;
}
}) 
})

Hope this helps,

W

[ckirk1975]

HI Wojtek,

I have tried your code on my Pivot and it doesnt seem to work. 

It seems to be picking up the column name for the  cell.innerHTML value and not the values themselves.

widget.on('ready', function(){

alert("in");

$("div[class*='--col'] div.table-grid__content__inner").map(function(i, cell) { 

alert(cell.innerHTML);

switch (cell.innerHTML ) {

case "1": cell.innerHTML='FAIL';

break;

case "2": cell.innerHTML='PASS' ;

break;

case "3": cell.innerHTML='MERIT';

break;

default: cell.innerHTML=cell.innerHTML;

}

}) 

})

My code has an alert of the cell value that shows its taking the column name.

Am i missing something?

Regards,

Ciaran

[Wojtek]

Hi Ciaran,

Not really sure, looks good to me.

I tested mine in Chrome and Edge, both works ok see below. We did another implementation of this recently and it worked fine too.