4 Replies Latest reply on Jan 25, 2019 10:16 AM by Steve Hawkes

    Display zero when no data

    Steve Hawkes

      I can't figure this out and haven't found an answer googling.  I have a very simple worksheet.  The only data on the sheet is the total number of records but the sheet is also using a filter based on the past 365 days.  If there's no data matching the filter criteria nothing appears on the sheet.  Is there a way to display zero? Thank you  

        • 1. Re: Display zero when no data
          Soham Wadekar

          Hi Steve,

           

          Did you try using the Zn function or ZN(Lookup(Sum(Measure),0)) or ISNULL()?

          You can also try formatting the Special Values for your measure. (Right click Measure -> format-> pane->special values )

          • 2. Re: Display zero when no data
            Daniel Lawrence

            How is Tableau supposed to know what dimensions you want 0 for?  If you had 143 dimensions and wanted a zero for each one or had 1 dimension and wanted one zero for it, they would both be generated out of no values.  Tableau reads off of data and if you are providing it no data then it has nothing to read.  There are not any "placeholders" or the sort like you could create in excel because the visualization is purely a function of the data.

             

            The most robust and scale-able way of handling this type of problem is to change your data source so that it provides entries for all dates you want to look at but then provides Nulls for every dimension you want to populate if it does not have a value.  This is called data densification.

            Then you can use Soham's suggestion above utilizing the ZN() function which gives a 0 value to any Null Value.

            • 3. Re: Display zero when no data
              Deepak Rai

              IFNULL(LOOKUP(SUM(Number of Records),0),0)

              • 4. Re: Display zero when no data
                Steve Hawkes

                Thank you for the suggestions.  As you can tell, I'm fairly new to Tableau so it's very helpful to see other ways of accomplishing what I need.