6 Replies Latest reply on Dec 1, 2016 7:34 AM by Katie Bueno

    How to show categories that do not exist in the data as zero

    Katie Bueno

      Hi everyone. I'm trying to get my table to show all of the answer choices to the questions, even if the answer choice does not exist in the data. I'm recreating the following table:

       

      I wrote the following syntax  to create the income range categories from the raw dataset (because these categories are not even, I did not use bins):

      I noticed that the categories that were not reported by any client in the data were not included in the table.

      How do I make it so that the categories that are not found in the dataset are still included in the table and labeled as 0?

      I've already tried "Table Layout" "Show empty columns" and "rows." But because this is one dimensional, not sure if that applies.

       

      Thanks,

      Katie

        • 1. Re: How to show categories that do not exist in the data as zero
          Nachiket Dekhane

          Hi Katie,

           

          You can refer to the discussion on this topic here: Re: Force Blanks to Show.

           

          Hope this helps.

           

           

          Thanks,

          Nachiket

          • 2. Re: How to show categories that do not exist in the data as zero
            Katie Bueno

            Thank for the reference, Nachiket. Unfortunately, i have already gone through this discussion and could not figure out how to fix my data specifically.

             

            I am new to Tableau, so I may just not be understanding the solution.

            • 3. Re: How to show categories that do not exist in the data as zero
              Nachiket Dekhane

              Hi Katie,

               

              Can you attach a packaged workbook? It would be difficult to identify the issue with your calculation without looking at the source data.

               

              Thanks,

              Nachiket

              • 4. Re: How to show categories that do not exist in the data as zero
                Katie Bueno

                Hello Nachiket,

                 

                I cannot publish the workbook since it's tied to client personal information.

                 

                The example I provided is regarding annual household income. The variable "Income" is a continuous variable and "IncomeID" includes the answer choices, "don't know" and "Refused."

                 

                Furthermore, some other string variables that I used in a CASE statement to create the answer choice categories have the answer choices that are missing from the output because there are no clients who reported those specific answers. They are not NULL, they just do not exist in the dataset and thus are not showing up in the table.

                 

                I'd like for my output to still show in the table or graph when the answer choice was not selected at all in the data. Here is another example showing how I created the educational attainment answer choice categories, and how the answer choice "refused" does not show in the table because there aren't any clients who refused the question:

                 

                 

                 

                My goal is to still have the "Refused" category in the table and show zero percent.

                 

                Thanks,

                Katie

                • 5. Re: How to show categories that do not exist in the data as zero
                  Ashish Chaudhari

                  Hi Katie,

                   

                  Try creating separate calculation for each one the case and put in the Rows. You will get the title as the Refused. You wll have to make a good use of Table calculations in order to get he percentage out of it.

                   

                  Please follow below example. I am using superstore for the same.

                   

                  Sample Calc

                  Furniture

                  if [Category]="Furniture" THEN [Number of Records]

                  END

                   

                  Create seprate calcs for Office supplies and Technology as above.

                   

                  Rename the Fake calculation.

                  This you can do when you get measure value option. You get it when you put more that one measure and choose crosstab.

                   

                  I hope this helps you in some way.

                   

                  -Ashish Chaudhari

                  • 6. Re: How to show categories that do not exist in the data as zero
                    Katie Bueno

                    Hello Ashish,

                     

                    This is how my data was set up originally, but I could not produce a percent. The issues I ran into was:

                      • not being able to do a table calculation to run a percent total since these answer choices are now individual answer choices,
                      • not being able able to have the table calculation of the percent of an answer choice only show for that answer choice.
                        • For example: I can't make it so that the tooltip only shows the percentage for the specific answer choice instead of showing or percentage or i couldn't have two columns showing both number of records and the percentage.

                     

                    Do you know how I can do this?

                     

                    Thanks,

                    Katie