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

    Display zero when no data

    Steve Hawkes
    Steve Hawkes Jan 24, 2019 12:50 PM

      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  

      • 100 Views
      • Categories: Formatting & Display
      • Tags:
        • 1. Re: Display zero when no data
          Soham Wadekar
          Soham Wadekar Jan 24, 2019 12:58 PM (in response to Steve Hawkes)

          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
            Daniel Lawrence Jan 24, 2019 1:04 PM (in response to Steve Hawkes)

            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
              Deepak Rai Ambassador Jan 24, 2019 1:17 PM (in response to Steve Hawkes)

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

              • 4. Re: Display zero when no data
                Steve Hawkes
                Steve Hawkes Jan 25, 2019 10:16 AM (in response to Daniel Lawrence)

                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.