6 Replies Latest reply on Dec 19, 2012 8:49 AM by Jonathan Drummey

    Show Zero Values in a Category

    John Mogielnicki

      Hello,

       

      I'm trying to get null values to show up as zeros in my dataset.  See packaged workbook for an example.  There's a category, Market, and a subcategory, Product Type.  There's no "Tea" Product Type in the South market.  Can I create a view where "Tea" still shows up in the South with a zero?

      Thank you.

        • 1. Re: Show Zero Values in a Category
          Jonathan Drummey

          I created a table with Market and Product Type, and added that to Tableau as a data source. Then I created a new worksheet with the new datasource as primary, put the Market and Product Type in the view, and added SUM(Number of Records) from the CoffeeChain db as secondary. This gets us the same total of records, only doesn't have a 0 for "Tea" in "South". Going to Format… for that and setting the Special Value for Text to 0 gets us the 0.

           

          If the relationship is hierarchical instead of many to many, you could do this using a primary group. See this KB article for details:

          http://kb.tableausoftware.com/articles/knowledgebase/how-create-primary-groups-secondary-source

           

          Example workbook attached.

          • 2. Re: Show Zero Values in a Category
            John Mogielnicki

            Thanks Jonathan.  Wish there was a way to do this without creating a new table, but this workaround works great.  Really appreciate the help.

            • 3. Re: Show Zero Values in a Category
              Jonathan Drummey

              You're welcome! I did have a later thought that you might have a query on your underlying data to generate the header table, and then set that up as an extract that on Tableau Server would get regularly updated. That way when you add a new category(ies) you wouldn't have to have the additional task to update the table.

              • 4. Re: Show Zero Values in a Category
                John Mogielnicki

                I like that idea.  Thanks!

                • 5. Re: Show Zero Values in a Category
                  Katy Means

                  Hello,

                   

                  I was able to utilize your solution to solve a similar issue, but am now stumped with another problem.

                   

                  To build off of the data set utilized here, is there a way to be able to modify the data so that zero values show up (i.e. Tea is zero in the South) and that I am able to filter by product?

                   

                  Thanks!

                  • 6. Re: Show Zero Values in a Category
                    Jonathan Drummey

                    Hi,

                     

                    The easy way to get Tea in the South to show up as zero is to set the Format->(chosen measure)->Pane tab->Special Values section->Text to 0.

                     

                    Using the above method, to do the filtering by Product, you'll need to pad out the data for the primary to include product so that can be in the level of detail for the view. If you want Product in the view, then it's easy to start filtering on that from the primary. If you want to filter on Product but not have that generating headers in the view, then instead of SUM(Number of Records) from the secondary, you'll need to have Product on the Level of Detail Shelf use a table calculation. This is due to needing Product in the view to get the blend to support filtering at the finer level of granularity, and the aggregation happening at the Product Type level. I set this up in the padded w/product2 and padded w/product filter worksheets in the attached.

                     

                    Something that wasn't made clear in the original series of posts is that the data is not padded at all because of the layout (all discrete dimensions on Rows). In a crosstab view, such as having Market on Rows and Product Type on Columns, then Tableau will do a form of padding called "domain completion" because it needs to generate a pane for every combination of the discrete values on Rows & Columns. In that case, there is a pane (that doesn't have a value) that can be accessed via a calc like ZN(LOOKUP(SUM([Number of Records]),0)). I set this up in the domain completion w/ZN LOOKUP in the attached.

                     

                    Jonathan