1 Reply Latest reply on Nov 23, 2016 5:46 AM by lenaic.ridinger

    Show Company Values when Division Values are Null

    Hillary Lincourt

      Hello,

       

      I'm working on Survey data and reporting the percent of Favorable responses ([Response Code] = 4 or 5) by Question.

       

      Users can select a demographic Parameter such as Gender, Race, Highest Degree, etc...

       

      For Gender, survey respondents were asked to choose one for four values; Female, Male, N/A or Gender Nonconforming/Transgender (Trans/GNC).

       

      I am reporting the values side by side for Division (user can select a Division, Department Family or Department Name) and Company.

       

      The issue I am having is when there are no favorable responses to a question for a Gender in a Division, neither the Division reports (this is expected) nor the Company values report (this is unexpected).

       

      For example,

       

      My Results for a particular question

       

      Parameter = Gender

      Division

           Female     Male       NA     Trans/GNC

                  43         66           1                        

                  54         83           1                   

             79.6%    79.4%     100%

       

      Company

           Female     Male        NA     Trans/GNC

               5914     2801       155                        

               7827     3530       222                   

             75.6%    79.4%    69.8%

       

      My Results if I split this into two worksheets (1 - Division & 2 - Company) and remove Division, Department Family and Department Name from Filters on the Company worksheet.  I would like these results on one worksheet so I can make a side by side bar graph.

       

      Division

                               Female     Male         NA     Trans/GNC

      Numerator                 43         66          1                        

      Denominator             54         83          1                   

      Percent                 79.6%    79.4%   100%

       

      Company

                               Female     Male        NA     Trans/GNC

      Numerator             5914     2801      155                   63                     

      Denominator          7827     3530      222                 117       

      Percent                75.6%    79.4%    69.8%          53.8%

       

      Calculations  - Note: [Seq ID] is a respondent

      Division - Numerator

      If [Response Code] = "4" or [Response Code] = "5"
      then

      {Fixed
      [Question]

      ,[Seq ID]
      ,[Response Code]
      ,[Division]
      ,[Department Family]
      ,[Department Name]
      ,[P.Parameter 1]
      :CountD([Seq ID])}

      end

       

      Division - Denominator

      {Fixed

      [Question]

      ,[Response Code 1-5]

      ,[Division]

      ,[Department Family]

      ,[Department Name]

      ,[P.Parameter 1]

      :CountD([Seq ID])}

       

      Company - Numerator

      {Fixed

      [Question]

      ,[Response Code Filter 4-5]

      ,[P.Parameter 1]

      :CountD([Seq ID])}

       

      Company - Denominator

      {Fixed

      [Question]

      ,[Response Code 1-5]

      ,[P.Parameter 1]

      :CountD([Seq ID])}

       

      Any suggestions on how I can report ALL Company values on the same worksheet as Division values when a Division Value is Null?

       

      Thank you for your time.

       

      Hillary

        • 1. Re: Show Company Values when Division Values are Null

          Hi Hillary Lincourt,

           

          The basic issue is that there doesn't seem to be any data for those combinations of dimensions you are using, and Tableau doesn't make up data when there isn't any. "No data" is different from Null.

          However, we can tell Tableau to generate a null when there isn't any data, and that's using the following table calculation: LOOKUP(SUM([Flag]),0).

           

          This table calc returns SUM([Flag]) for the current row, and Null when no value is found. Then you can wrap that in the IIF(ISNULL()) operation to return 0 or a value. alternatively, you could just do the lookup and then Format the calculation to set 0 to be the Text for Special Values.

           

          You can find more information about this process here: Populating Empty Cells with Zeroes or Existing Data | Tableau Software

           

          Please let me know if it solved  your issue with these null values in your Gender list