4 Replies Latest reply on May 26, 2013 8:02 PM by ruth.wyer

    Shwoing null values on axis for text based dimension

    ruth.wyer

      Hi,

       

      I have need a always show a list of action codes on an axis (P1-P5).  My user wants these 5 codes always there regardless of whether there was any data that month.  Because these are not dates, I used the following calculated field to make nulls into zeros

       

      http://kb.tableausoftware.com/articles/knowledgebase/null-values

       

      That seemed to work but I would need to create a calculated field for every measure that I need.  Not a problem if that's the only way to do it.  My question now it that it seemed to force my colours into sequential when I don't want that.  I have got around it by forcing values to end at the start range but it seems a cluncky solution.  I'm afraid I can't upload a workbook as it is confidential data.  I'm hoping the screenshot may assist.  Does anyone know if this is the only way I can handle this issue?

       

      I am on Tableau 7 and the data connection is to a cube.

       

      Thanks

      Ruth

        • 1. Re: Shwoing null values on axis for text based dimension
          Dana Withers

          Hi Ruth,

           

          I'm not sure, but I think if you are filtering by month and filter out certain action codes that exist somewhere else in the dataset, it is possible to show nulls by choosing Analysis => Table Layout => Show empty columns. But if the data is not in the dataset, I don't think there is another way.

          For example, in the Superstore sample dataset I do a graph of sum of sales by region. Then I filter sum of sales to have a minimum value of 320,006. That hides all regions except international and even hides some of the months. To show those months and regions I can choose Show empty columns.

          However to show a column like "Neverland" which is not a region in my dataset anywhere, would not be possible like that.

           

          I have used this in one of my books by adding a dummy row for each column I wanted to see, then filter out the dummy row when it comes to showing data and finally choosing empty rows to make sure my dummy rows give me the columns I want to see even if their data is filtered out.

           

          So it depends on whether or not P3 and P5 are in your dataset somewhere (even if they have been filtered out).

           

          Does that help?

           

          Dana

          1 of 1 people found this helpful
          • 2. Re: Shwoing null values on axis for text based dimension
            ruth.wyer

            Hi Dana,

             

            Yes, that was helpful.  Thank you.  I know how hard it is to help someone when all you have is a picture to go off so I appreaciate your reply.  P3 and P5 are not in my dataset at the moment.  As the month progresses and more records are written (they are action based) they may start to appear but the user doesn't want the chart to jump around.  They are available for me to select because they are a dimension.  There are no rows for them as yet.  I don't know much about Tableau or Cubes but I don't think adding dummy rows will be an option for me in a cube that refreshes each night and over which I have no control.  So it sounds like the Null to Zero option is the way to go?

            Thanks

            Ruth

            • 3. Re: Shwoing null values on axis for text based dimension
              Dana Withers

              Hi Ruth,

               

              I think you are right. If the Show Empty Columns option does not work then Null to Zero might be the way.

               

              Dana