Replace Null values or N/A's with zeros in Pivot Grid
In Widget editor/Edit Script option, place script (below), click save, and refresh widget page.
To replace N/A values:
widget.on('ready', function(){
$('td[class*=p-value] div').map(function(i, cell) {
if(cell.innerHTML == "N\\A" ) cell.innerHTML=0;
})
})
Or to replace null values:
widget.on('ready', function(){
$('td[class*=p-value] div').map(function(i, cell) {
if(cell.innerHTML == " " ) cell.innerHTML=0;
})
})
Before:
After:
-
Hi Andrea,
It seems in v5.5 the removed the N/A 's completely. When using this script in v 5.5 please remove the "N\\A" and change it to "". See below.
widget.on('ready', function(){
$('td[class*=p-value] div').map(function(i, cell) {
if(cell.innerHTML == "" ) cell.innerHTML=0;
})
})Let me know you still have issues!
Cheers,
Nishad Amin
COMPAS Technology
-
This can also be used for replacing values with text. For instance, we wanted to display an employee schedule in a pivot. We were able to do it by modifying this code as below:
widget.on('ready', function(){
$('td[class*=p-value] div').map(function(i, cell) {
cell.innerText.replace(/[^\d.-]/g, '')
if (cell.innerHTML == 0)
{
cell.innerHTML = 'Off';
}
else if (cell.innerHTML == 1)
{
cell.innerHTML = 'Office Work';
}
else if (cell.innerHTML == 2)
{
cell.innerHTML = 'Traveling';
}
else if (cell.innerHTML == 3)
{
cell.innerHTML = 'Half Day';
}
else
{
//replace w/ empty space
cell.innerHTML= '';
}
})
}) -
Thanks for the amazing posts and updates, if anyone would like this setting to be applied also on rows in addition to values, and to have another option of showing empty tabs instead of 0's, you can use the following script:
widget.on('ready', function(){
$('td[class*=p-value] div, td[class*=p-dim-member] span', element).map(function(i, cell) {
if(cell.innerHTML == "N\\A" ) cell.innerHTML='';
})
})Enjoy!
-
@Jim,
The table widget functions differently from the pivot, as the "N/A" or blank cell was the absence of an aggregate value.
Since the Table Widget is a tabular representation of the data, we use a script to Find & Replace values in a particular column, where the underlying data is irrelevant to the end user. I have attached a sample dash board for ECommerce on how we do this.
See script below as well to outline how we achieve this. The example dashboard attached shows that for the column Country ID, how to replace the value 142, to 0.
widget.on('domready', function(se, ev){
var table = document.getElementById('grid');
var rows = table.rows;
var rowcount = rows.length;
var r;
var cells;
var cellcount;
var c;
var cell;
var removeLinkDecoration = false;
var columnName = "Country ID"; /*column you want to replace*/
var column = $(rows[0]).find('th:contains(' + columnName + ')').index();for(r=1; r<rowcount; r++) {
cells = rows[r].cells;
cellcount= cells.length;
cell = cells[column].innerHTML;
//console.log(rows.columns);var find = "142";
var replacewith = "0"if(cell == find){
$(cells[column]).html(replacewith);
}
};
});
SampleECommerce_replaceval.dash -
Hi All,
Please use the following scripts for both Tables or Pivots:
/*for table widgets*/ widget.on('domready', function(se, ev){ _.each($(element).find("td:not(:has(*)):contains('N\\A')"),function(e,i){ $(e).text("MyString"); }) });/*for pivots*/ widget.on('domready', function(se, ev){ _.each($(element).find("span:not(:has(*)):contains('N\\A')"),function(e,i){ $(e).text(""); //put nothing }) }); -
Arik,
We noticed that v5.8 reverses the N\A to N/A so we modified the script for the pivot and this worked for us:
/*for pivots*/
widget.on('domready', function(se, ev){
_.each($(element).find("span:not(:has(*)):contains('N/A')"),function(e,i){
$(e).text("Not Defined"); //put something
})
}); -
Hi All
Use this script to replace nulls with zeros while applying the number formatting from the value itself. (thanks Tak)
widget.on('processresult',function(widget,args){
// Set some defaults
var defaultValue = 0;
// Get the HTML table
var tableEl = $('<div>').append($(args.rawResult.table));
// Get all the value cells that are empty
var cells = $('td.p-value:empty',tableEl);
// Set values for those empty cells
cells.text(defaultValue);
// Write the table back to the query result
args.result.table = tableEl.html();
}) -
Hi,
I would like to do something a bit different which is not answered here.
I want to display all values in a pivot table (lets say all Products) even if there is no value (Sales) in it.
The difference from what you are saying is that some Products are not shown because they are outside of the dashboard filters I used (and not because there is not Product name in dimension table)
Let's say for example that I select from my dashboard filters, Store = Camden.
I want the pivot table to display all products and not just the ones that were sold in Camden. -
Hi Michael,
Unfortunately this doesn't work.
I think I understand the issue now It is how Sisense works.
When using a dashboard filter, it's like doing an INNER JOIN so the other data disappears. It's like it's not even there.
If there was a way to say this is a FULL OUTER JOIN then I would be able to view other data too. -
The way of doing that would be through measure filters. You could use this add-on https://documentation.sisense.com/filtered-measures/ to setup dynamic filter on measure level. Then disable dashboard filter to not affect entire widget(in edit mode of the widget on the right hand side in filters section you have a list of all dashboard filters; there you can choose which filters should affect this particular widget). I think that might solve the issue.
-
We are also looking for a boolean function that could be added to a widget formula to replace null values with 0s, as in Michael's example.
This would be a) more intuitive, b) easier for any users to edit, and c) flexible for each column instead of applying to the whole widget.
Thanks,
Carly
-
Try multiplying with 1 in Metric formula. It display zero with the format you already applied on the metric you set whether it is currency or number. I have not tried for percentage yet.
If currency is set to display $, Sum(A)*1 will return $0 for NULL values
If it number, Sum(A)*1 will return 0 for NULL values
-
Agreed with Davi Chan -- None of these are working. There should be zeros all over this grid.
-
I think the solution has been realised here: https://support.sisense.com/hc/en-us/articles/115013195307-How-to-perform-Left-Join-in-the-Dashboard
If your use case is successful with this solution, do let us know!
-
Davi, it does seem to help on a single dimension, but if there are multiple dimensions, it fails.
See below. Using the Sample_Ecommerce cube:
- List of Countries only: Benin visible.
- Add Revenue, Benin not visible
- Add Revenue + LeftJoin value of 0 on the country DIM, Benin visible.
- List of Countries with Year selected from date in Fact Table, Benin not visible
- Add Revenue, Benin not visible
- Add Revenue + LeftJoin value of 0 on the country DIM, Benin not visible.
I also tried adding a second dimension table "Year" and joining on that as well, creating a fake Left join in that table and adding Revenue + CountryLeftJoin + YearLeftJoin and that failed.
Please sign in to leave a comment.
Comments
41 comments