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

[frankmol]

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.

[Vik]

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

Documentation reference. 

[frankmol]

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!

[Sijo1995 · Accepted solution]

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]

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.