cancel
Showing results for 
Search instead for 
Did you mean: 

nulls to 0 in pivot

zach_myt
10 - ETL
10 - ETL

I am trying to get nulls in a pivot to show as 0's, which seems like it should be very basic built in functionality. I have tried If's, case's, and jquery scripts and nothing seems to work. I can do this at the SQL level buy I am trying to count in the widget and not in the query. 

I have read threads for 9 years ago where this was still a problem. How is this still an issue and not a quick function or easy setting???

What I've tried:

if(isnull(DUPCOUNT([device])),0, DUPCOUNT([device])) <-- this counts everything fine but blanks instead of 0

case when isnull(DUPCOUNT([device])) then 0 else DUPCOUNT([device]) end <-- this counts everything fine but blanks instead of 0

In the edit script section:

widget.transformPivot(
{
     type: ['member']
},
function (metadata, cell) {

if(cell.value === '') {
      cell.content = '0';
    }
  }
);

AND 

 
widget.on('ready', function(){
 
$('td[class*=p-value] div').map(function(i, cell) {
      if(cell.innerHTML == "&nbsp;" ) cell.innerHTML='0';
      })
    })
})

Any ideas?

1 ACCEPTED SOLUTION

Sijo1995
9 - Travel Pro
9 - Travel Pro

Hi @zach_myt ,

This script for Linux region

widget.transformPivot(
    {
        type: ['value']
    },
    function setCellBackground(metadata, cell) {
        if(cell.content.trim() == '')
			cell.content = '0'
    }
)

 

Thanks

Sijo

View solution in original post

2 REPLIES 2

Sijo1995
9 - Travel Pro
9 - Travel Pro

Hi @zach_myt ,

This script for Linux region

widget.transformPivot(
    {
        type: ['value']
    },
    function setCellBackground(metadata, cell) {
        if(cell.content.trim() == '')
			cell.content = '0'
    }
)

 

Thanks

Sijo

zach_myt
10 - ETL
10 - ETL

Simple and it works correctly, thank you!