Forum Discussion

frankmol's avatar
frankmol
Cloud Apps
06-13-2023
Solved

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

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.

 

Customizations
Sisense.JS
  • Sijo1995's avatar
    Sijo1995
    06-15-2023

    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

4 Replies

Replies have been turned off for this discussion
  • Darwin's avatar
    Darwin
    Cloud Apps
    06-15-2023

    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.

  • Vik's avatar
    Vik
    Sisense Employee
    06-14-2023

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

    Documentation reference. 

    • frankmol's avatar
      frankmol
      Cloud Apps
      06-15-2023

      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's avatar
    Sijo1995
    Cloud Apps
    06-15-2023

    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