2 Replies Latest reply on Apr 18, 2013 10:36 AM by RICHARD MAC NAMARA

    How to show zero's for data items not in source data


      Hi all, a simple mock up of my help desk ticket data looks like this:


      Incident IDSeverity
      1Severity 3
      2Severity 3
      3Severity 3
      4Severity 4
      5Severity 4
      6Severity 5
      7Severity 5
      8Severity 6
      9Severity 6



      My Excel report looks like this:


      Severity# of Incidents
      Severity 10
      Severity 20
      Severity 33
      Severity 42
      Severity 52
      Severity 62



      My Excel report uses COUNTIF's to total the counts from the data. Severity 1 and Severity 2 items are not in the data and display 0's in the report. In Tableau (7.0) the Severity 1 and Severity 2 lines will not even show up when I count the records by severity (see attached viz and data extract). Is there a way to pad the data so all possible severity's will display even if they don't exist in the data.


      Thanks, Rick

        • 1. Re: How to show zero's for data items not in source data
          Justin Emerick



          Your data set doesn't have any records that indicate Severity other than 3, 4, 5, 6. As a result, there is no logic in the data that would suggest that Severity begins at 1 and can be as high as 9.


          An easy way to fix this is to simply open NOTEPAD on your desktop and type the following.



          Severity 1

          Severity 2

          Severity 3

          Severity 4

          Severity 5

          Severity 6

          Severity 7

          Severity 8

          Severity 9


          Next, copy that text to your clipboard.


          In Tableau, in the header click "Data" and select "Paste Data" This will create a data source on your workbook called "Clipboardxxxxxxx"


          Now, Click the "Data" header again, and select "Edit Relationships." Making the clipboard data source your primary, create a relationship to your example data set on the "Severity" column. (Tableau should have this as an automatic relationship) - see image.




          With this set up, you'll be able to drag the Severity Dimension from the Clipboard data set, and use the values from your example data set.


          If you want NULLs to show up as "0" simply follow the instructions from this KB article: http://onlinehelp.tableausoftware.com/v6.1/pro/online/en-us/i1139656.html


          Hope that helps.



          • 2. Re: How to show zero's for data items not in source data

            Thanks Justin, that works perfectly.