Replace Values with Text - Pivot Table Widget

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:

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='-';
}
}) 
})