cancel
Showing results for 
Search instead for 
Did you mean: 
Community_Admin
Community Team Member
Community Team Member

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:

Community_Admin_0-1634651775311.png

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")
})
Rate this article:
Comments
wallingfordce
10 - ETL
10 - ETL

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
10 - ETL
10 - ETL

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

Wojtek
9 - Travel Pro
9 - Travel Pro

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
9 - Travel Pro
9 - Travel Pro

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

rvickersevotix
9 - Travel Pro
9 - Travel Pro

I have no idea if this will ever help anyone but I have to work with a very limited set of values and its very hard to get new stuff added in our elasticubes.

So, if you are trying to input date fields into your Pivot and dont have access to the elasticubes or the date as an integer, then this might be for you.

Firstly, you need to work out your days, I did this using ddiff:

DDiff([Days in RequiredBy1],now([Days in RequiredBy1]))

This gave me a total number of days until required, I then used javascript to convert that days. This is where we add a script to the widget telling it to work out todays date, add the days too it, get the new date and present it back to us in a nice dd/MM/yyyy format.

widget.on('domready', function() {
    setTimeout(function() {
        $("tbody tr", element).each(function() {
            $("td", $(this)).each(function() {
                if($(this).attr("fidx") == "3") {
                    // Get the value from the "val" attribute, if it exists
                    let val = parseInt($(this).attr("val"));

                    // If "val" is not defined, try getting the value from the child div element
                    if (isNaN(val)) {
                        val = parseInt($(this).find(".p-value").text().replace(',', ''));
                    }

                    // Calculate the expiry date by adding the value from "val" to today's date
                    const expiryDate = new Date(Date.now() + val * 86400000); // 86400000 is the number of milliseconds in a day

                    // Format the date string in UK short date format
                    const formattedDate = expiryDate.toLocaleDateString('en-GB');

                    // Replace the text content of the cell with the formatted date
                    if (formattedDate !== "Invalid Date") {
                        $(this).find(".p-value").text(formattedDate);
                        $(this).attr("val", ""); // Remove the "val" attribute, if it exists
                    }
                }
            });
        });
    })
});

You should get something that looks like this:

Screenshot_172.png

I also used a similar method above to change the values I was getting out:

if(([# of unique EntityReference1],[TNAStatus]) > 0,111,
   if(([# of unique EntityReference1],[TNAStatus1]) > 0,222,
   if(([# of unique EntityReference1],[TNAStatus2]) > 0,333,
   if(([# of unique EntityReference1],[TNAStatus3]) > 0,444,0)))) 

Then in the script, I would change 111 to equal "Completed" etc

widget.on('ready', function(){
$('td[class*=p-value] div').map(function(i, cell) { 
switch (cell.innerHTML ) {
case "111": cell.innerHTML='Completed';
break;
case "222": cell.innerHTML='In Progress';
break;
case "333": cell.innerHTML='Not Started';
break;
case "444": cell.innerHTML='Optional';
break;
case " ": cell.innerHTML='-';
break;
//default: cell.innerHTML='-';
}
}) 
})
Wojtek
9 - Travel Pro
9 - Travel Pro

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
7 - Data Storage
7 - Data Storage

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.

ckirk1975_0-1688392847420.png

 
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.
 
ckirk1975_1-1688392953532.png

Am i missing something?

Regards,

Ciaran

 

 

 

 

 

Wojtek
9 - Travel Pro
9 - Travel Pro

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.

pass_fail.png

ssoltz
8 - Cloud Apps
8 - Cloud Apps

All,

 

Does this script only work in Elasticube Environments? I cannot get this to work in my Live Data environment.

Thank you!

ssoltz
8 - Cloud Apps
8 - Cloud Apps

Hi...checking back in...can anyone answer the above simple question? Does this only work in Elasticube environments or should this work in a Live Data environment?

Version history
Last update:
‎10-19-2021 06:57 AM
Updated by:
Contributors