cancel
Showing results for 
Search instead for 
Did you mean: 

Replacin 'NULL' with '0' in in calculations for column chart and pivot table

frankmol
8 - Cloud Apps
8 - Cloud Apps

Hi all,

 

For a culumn chart  I calculated averages and divided the percentages into segments, the percentages do not add up to 100%

After some searching I found out that this is because the 'NULL' values ​​are not seen as '0' and therefore the calculation is not performed properly.

See the pivot table below, for this pivot table I want to replace the null values ​​with '0'.

As can be seen in the updated table, it can be seen that the table generates null values ​​but does not include them in the calculation of averages.

I've already tried to solve this using IFs , but without success.

 

1 ACCEPTED SOLUTION

Sijo1995
9 - Travel Pro
9 - Travel Pro

Hi @frankmol ,

https://community.sisense.com/t5/build-analytics/nulls-to-0-in-pivot/td-p/14777 

Please try this script

 

 

function replaceNullsWithZeros(data) {
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      if (data[i][j] === null) {
        data[i][j] = 0;
      }
    }
  }
  return data;
}

// Replace all NULL values in the pivot table with 0
widget.on('processresult', function(sender, ev) {
  ev.result = replaceNullsWithZeros(ev.result);
});

 

And please read this document-https://community.sisense.com/t5/build-analytics/nulls-to-0-in-pivot/td-p/14777

 

Thanks

Sijo

View solution in original post

4 REPLIES 4

Vik
Sisense Team Member
Sisense Team Member

Hey!
Have you tried to check the option "Display Missing Values as Zeros"?
It should be available for the column chart.

Documentation reference

frankmol
8 - Cloud Apps
8 - Cloud Apps

Hey Vik,

Yes i have,  While it successfully displays the '0' value, I actually need it to be included in a calculation rather than just for display purposes.  The column chart I'm working on relies on the same calculation as the table seen below, the expected markt total for Client 2 should be  'Primair 5,1%' and 'Secundair 12%' 

Hopefully this wil help!

sisense.png

Sijo1995
9 - Travel Pro
9 - Travel Pro

Hi @frankmol ,

https://community.sisense.com/t5/build-analytics/nulls-to-0-in-pivot/td-p/14777 

Please try this script

 

 

function replaceNullsWithZeros(data) {
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      if (data[i][j] === null) {
        data[i][j] = 0;
      }
    }
  }
  return data;
}

// Replace all NULL values in the pivot table with 0
widget.on('processresult', function(sender, ev) {
  ev.result = replaceNullsWithZeros(ev.result);
});

 

And please read this document-https://community.sisense.com/t5/build-analytics/nulls-to-0-in-pivot/td-p/14777

 

Thanks

Sijo

Darwin
Sisense Team Member
Sisense Team Member

You could try wapping the column with nulls in an IsNull() function.  For example: IsNull(<NullableCloumn>, 0).  This would need to be a custom column or part of a custom table to be used at the dashboard level.