3 Replies Latest reply on Dec 2, 2010 1:09 PM by Joe Mako

    Replacing Null Values with a 0 in a Crosstab

    Kris Willis

      I have a report where I am attempting to create a cross-tab of response counts by deparment and facility (department is the vertical axis and facility is the horizontal axis).  However, if the department did not have any responses the cross-tab is showing a NULL value.  I need to show a 0 instead. 

       

      I have attempted CASE statements, IF statements, converting the number value to a string,....

       

      My datasource is an extract made form an Excel file.

        • 1. Re: Replacing Null Values with a 0 in a Crosstab
          Ross Bunker

          Hi Kris,

          Technically, if the department didn't have any responses, the crosstab is not showing a NULL value, there is simply nothing there at all.  It's a subtle distinction, but a very important one.  While tableau let's you change NULLs to other values via a number of calculation functions, if there is no data at all, Tableau won't manufacture the rows for you.  In fact, it may be desirable to know that there is no data at all in some cases.

           

          There are a couple of ways to deal with this.  One is easy, one is more complicated than it should be.

          The simplest is to add the missing rows of data with NULLs or zeros.  That may not be possible.

           

          The other is to generate a separate excel file that has all department facility combinations in it, but no survey information.  Connect to that as a separate datasource, then put those fields on the axis, and use the measure from the first datasource on text.  If the department/facility fields have the same name (or are renamed to be the same) in both datasources, Tableau's data blending feature will bring in the survey data from the original spreadsheet, and use NULLs for data that is absent.

          To then turn those NULLs into 0s is a bit tricky but do-able.

          If your first spreadsheet's datasource is SurveyData and the new one that i suggested is called DeptFac, then in DeptFac you'd create a calculation of the form:

          IIF(ISNULL(MIN([SurveyData].[Number of Records])*MIN([Number of Records])), 0, SUM([SurveyData].[Responses]))

           

          This is a tricky little calculation (we need to make this simpler).  Essentially, it checks to see if the SurveyData had a matching row.  If it did, it displays SUM([SurveyData].[Responses]), if it doesn't, it displays 0.  How does it do this?  The key is the ISNULL().  Inside there it multiplies the min of SurveyData number of records (always 1 if a join happened, NULL otherwise) by the min of DeptFac number of recrods (always 1).  Since, MIN([Number of Records]) comes from the DeptFac datasource, this multiplication happens after the join.  Now, since MIN([Number of Records]) from either datasource is always 1, the only way this will be NULL is if [SurveyData].[Number of Records] is NULL which can only happen if a join didn't occur.  As i said, we really need to find an easier way to do this,but since the [Number of Records] column always in every datasource, this is a portable way to answer the question 'did this row have any data from the other datasource'.

           

          Hope this helps.  Sorry we don't have a simpler way to deal with this issue yet.  It is a long-standing issue, but one that we are actively looking into more deeply.

           

          regards,

          :)ross

          1 of 1 people found this helpful
          • 2. Re: Replacing Null Values with a 0 in a Crosstab
            Marc Pujda

            I had the same challenge.  Didn't want to go the route of creating a bogus record to compensate for the lack of a valid one in the secondary data source, though I knew that was an option.  I was more interested in figuring out the "how" using calculated fields in a "data blending" workbook.  You've shown me the light.

             

            Thanks Ross, much appreciated,

            Marc

            • 3. Re: Replacing Null Values with a 0 in a Crosstab
              Joe Mako

              Another option is a calc field like this created in your primary source:

               

               

              IF NOT ISNULL(ATTR([Secendary_Source].[Dimension])) THEN
              
               SUM([Secendary_Source].[Measure])
              ELSE 0 END
              


               

              Where

              [Secendary_Source].[Dimension]
              is the most detailed field of the Data Blend Relationship join.