Replace Null values or N/A's with zeros in Pivot Grid

Comments

34 comments

  • Avatar
    Andrea

    HI Nishad, 

    This javascript is not working for Sisense V5.5.  May I know what changes need to be made to the script?

    Thank you

    Andrea

     

     

    0
    Comment actions Permalink
  • Avatar
    Nishad Amin

    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

    0
    Comment actions Permalink
  • Avatar
    Joseph Peters

    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= '';
    }
    })
    })

    0
    Comment actions Permalink
  • Avatar
    Nishad Amin

    updated for v5.7, 

     

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

    0
    Comment actions Permalink
  • Avatar
    Ori Sandler

    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!

    0
    Comment actions Permalink
  • Avatar
    Jim Thomas

    Tried this with the Table Widget on Version 5.8.1.19.  Doesn't function.  Any advice for making that work?

    0
    Comment actions Permalink
  • Avatar
    Nishad Amin

    @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
    0
    Comment actions Permalink
  • Avatar
    Jim Thomas

    Nishad,

    Thanks.  Worked for us.  We were hoping to change all "N\A" in the table at once rather than a column at a time.

    Regards,

    Jim

    0
    Comment actions Permalink
  • Avatar
    Andrew Block

    Nishad,

    That worked great for 1 column, is there a way to add multiple columns to use this functionality ?

     

    Thanks

     

    0
    Comment actions Permalink
  • Avatar
    Arik

    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	
    	})
    });
    
    
    
    1
    Comment actions Permalink
  • Avatar
    Jim Thomas

    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 
     })
    });

    1
    Comment actions Permalink
  • Avatar
    Diana

    Hi,

    When there is no data in the table , the column chart should display as zero instead of displaying no data ,Can you please suggest how to configure this.

    Appreciate you help in advance.

    Thanks,

    Diane

    0
    Comment actions Permalink
  • Avatar
    Ramon Lopez

    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();

    })
    3
    Comment actions Permalink
  • Avatar
    Mahesh J

    How to get value from a Smart label?

    I need to dynamically set colors based on widget value.

    0
    Comment actions Permalink
  • Avatar
    Alexandros Antoniou

    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.


    0
    Comment actions Permalink
  • Avatar
    Michael Becker

    Hi Alexandros,

    please correct me If I'm wrong but you want to display full list of products and to show numbers only for those, which were sold at a particular location?

     

    0
    Comment actions Permalink
  • Avatar
    Alexandros Antoniou

    Hi Michael,

    Yes that's it. 

    And imagine the Location filter is a dashboard filter.

    So every time you select a location, the number of rows of the pivot stay the same and it's just the values that change.

    0
    Comment actions Permalink
  • Avatar
    Michael Becker

    Then I think instead of doing a simple SUM in your Pivot, you could create a following measure:

    IF(ISNULL(SUM(value)) = true, 0, SUM(value)). That should replace N/A to 0 on calculation level and show all the lines.

    Hope it helps.

    Michał

    0
    Comment actions Permalink
  • Avatar
    Alexandros Antoniou

    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.


     

    1
    Comment actions Permalink
  • Avatar
    Michael Becker

    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. 

    0
    Comment actions Permalink
  • Avatar
    Alexandros Antoniou

    Hi Michael,

    This widget was the closest I could get.

    Unfortunately there are various things that don't work. 

    I will continue this discussion with them.

    Thanks! 

    0
    Comment actions Permalink
  • Avatar
    Carly Smallwood

    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

    1
    Comment actions Permalink
  • Avatar
    Sarathy Ganesan

    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

     

     

    1
    Comment actions Permalink
  • Avatar
    Davi Chan

    We still really need this feature to only show null or blank values. While trying Sarathy Ganesan's method above changed the value of nulls to 0, we are still unable to then filter only 0 values. 'No Result' outcome is still reached..

    3
    Comment actions Permalink
  • Avatar
    Brian Wilson

    Agreed with Davi Chan -- None of these are working. There should be zeros all over this grid.

     

    2
    Comment actions Permalink
  • Avatar
    Jeremy Naiden

    Any progress here?

    1
    Comment actions Permalink
  • Avatar
    Catharine Lindsay

    Any updates?    Thank you

    1
    Comment actions Permalink
  • Avatar
    Davi Chan

    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!

    0
    Comment actions Permalink
  • Avatar
    Brian Wilson

    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:

    1. List of Countries only: Benin visible.
    2. Add Revenue, Benin not visible
    3. Add Revenue + LeftJoin value of 0 on the country DIM, Benin visible.
    4. List of Countries with Year selected from date in Fact Table, Benin not visible
    5. Add Revenue, Benin not visible
    6. 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.

    1
    Comment actions Permalink
  • Avatar
    Brian Wilson

    *tap tap tap*

    Is this thing on?

    2
    Comment actions Permalink

Please sign in to leave a comment.