Change Grand Total row to display Difference in Pivot Widgets
In cases where we want to display the difference between two columns instead of using the default Grand Totals in Pivot widgets:
Include the following widget script:
function removeCommas(num){
numClean = num.replace(/,/g,"");
return numClean;
};
widget.on("domready", function(){
/* Set text to be shown grand total row*/
var diff = "Difference";
/* set variables and clean local for reading */
var num1 = removeCommas($("#prism-mainview > div > div.errvil-warpper > div > div > div > pivot > div > div.p-fixed-cols > div > table > tbody > tr:nth-child(1) > td.p-value.p-first-data-col > div").text());
var num2 = removeCommas($("#prism-mainview > div > div.errvil-warpper > div > div > div > pivot > div > div.p-fixed-cols > div > table > tbody > tr:nth-child(2) > td.p-value.p-first-data-col > div").text());
/* Change label text*/
$("#prism-mainview > div > div.errvil-warpper > div > div > div > pivot > div > div.p-fixed-cols > div > table > tbody > tr:nth-child(3) > td.p-grand-total-row-head.p-first-data-col > div > div > span").text(diff)
/* Change difference value and add locale format*/
$("#pivot_ > tbody > tr:nth-child(3) > td.p-value.p-first-data-col.p-total-row-val > div").text((num1 - num2).toLocaleString());
});
console.log("background script run");
Result:
Disclaimer:
* Tested on version 7.0
* Only works with 2 row Pivot widgets
-
How do I do this for the pivots "Columns grand totals" instead of for the "Row grand totals"? I've a pivot with totals that I'm using a date in "Days" format in the Columns (so I'm breaking by the day on the pivot). Initially I'm comparing the results of the two days, and I would like the grand total for the days Columns to be a difference total instead of a grand total as doing above.
Thanks,
Bobbie Peteson
-
Also I need it for a 1-row pivot. So that can make the widget a Measure Changer Widget.
See this link: https://support.sisense.com/hc/en-us/community/posts/221225468-The-Measure-Changer-Widget-
Please sign in to leave a comment.
Comments
4 comments