2 Replies Latest reply on Apr 26, 2016 8:06 AM by Andreas Birkenmayer

    How to treat “No Data available” values in Tableau?

    Andreas Birkenmayer

      Hi Tableau Gurus,

      I have a question concerning the treatment of  “No Data available” values in Tableau.

       

      Recently I updated a Tableau dashboard showing European Agricultural Census data and farm segmentation from the European statistical database Eurostat. The original data from Eurostat contain two character signs: One indicating “No data available” and another one indicating “Data not disclosed” (privacy restriction).

       

      As I cannot load character signs in numerical columns and NULL values can create erroneous results (5 + NULL = NULL), I replaced those signs with zero while importing.

       

      However, this is a simplification. For example if farm numbers per size class show zero in a certain class, this means that in that country there were no farms in this size class. “No data available” or “Data not disclosed” would mean that there probably farms in this class, but the figures are not available.

       

      So if the case  “No data available” / “Data not disclosed”  occurs frequently for certain parameters or countries, this might create a bias in the resulting charts. For example the viewer of a chart might conclude that for a certain year the number of European farms within a class has decreased considerably, but in reality just more countries did not disclose or provide figures than in the last year.

       

      So here is my question: Is somebody aware of a methodology to treat "No data available" values, avoiding the mentioned problems?

        • 1. Re: How to treat “No Data available” values in Tableau?
          Simon Runc

          hi Andreas,

           

          I can think of a couple of ways of handling this...one would be to allow NULLS and then when you want to add things together (in Tableau) using ZN()...5 + NULL = NULL but ZN(5)+ZN(NULL) = 5

           

          The other way (as it sounds like these additions might take place in the loading, so wouldn't be able use ZN) is allow the field in as a String...and then create a calculated field in the form

           

          IIF([field] = 'No data available' OR [field] = 'Data not disclosed', NULL, INT([field])) //or use FLOAT([field]) if it's a decimal

           

          You can then use that field for any calculations.

           

          hope this helps, but if this doesn't solve your issue please post back (letting me know why) and we can probably find a way to handle that situation too.

          1 of 1 people found this helpful
          • 2. Re: How to treat “No Data available” values in Tableau?
            Andreas Birkenmayer

            Hi Simon, thanks for the answer, you sort of brought me back on the right track. I had converted the "data not available" values to zero to avoid null. Now I tested to convert to null values as you suggested, which does not negatively affect the charts. Then, using calculated fields, I can create separate charts to show the count of null values (= "data not available"). This will help the users to check for a potential bias.

             

            Thanks and regards, Andreas Birkenmayer