7 Replies Latest reply on Jul 9, 2012 7:58 AM by Jonathan Drummey

    How to break lines for NULL values

      I have an issue where Tableau plots lines for data that is not there, see attached image. I'd like the green line to be broken from 7th to 28th June, since there is no data for that period. I've tried setting formatting of marks for Special Values to "Hide (break line)", but it doesn't seem to have any effect.

       

      Can anyone enlighten me? The packaged workbook is attached.

        • 1. Re: How to break lines for NULL values
          Joe Mako

          You can use some custom SQL like:

           

           

          SELECT [Combos].[date] AS [date],
          
             [Sheet1$].[num] AS [num],
            [Combos].[type] AS [type]
          FROM [Sheet1$] RIGHT JOIN
          (SELECT DISTINCT
            [Sheet1$].[date] AS [date],
            [Sheet1a].[type] AS [type]
          FROM [Sheet1$], [Sheet1$] AS [Sheet1a]) AS [Combos]
          ON
            [Sheet1$].[date]=[Combos].[date] AND
            [Sheet1$].[type]=[Combos].[type]
          


           

          to pad the data, and then you can hide and break as in the attached.

           

          Tableau needs to have a null value that it can see in order to hide and break on a null value. If you set it to Show null marks, and it does not go to the zero line, there is no null record available to Tableau for you to hid and break.

           

          In a crosstab setup with table calculations, Tableau can pad the data, but having the lines in the same pane is not a crosstab for tableau, so the only way I know to get the results you are looking for is to pad the data prior to Tableau, either with custom SQL like the above, or with a data transformation/ETL operation.

          • 2. Re: How to break lines for NULL values

            Thanks! I'm guessing this has to be done with extracts. My question is, won't the extract take up unnecessary amounts of space when we pad the data like this? In our case, we have views we'd like to filter by several dimensions, each containing thousands of distinct values. If we have to pad the data as mentioned above, we might suddenly have trillions of rows instead of millions...

             

            Are there any plans to be able to do this another way, for instance be able to set it in preferences? It seems like this is a relatively complex workaround to a very simple problem.

            • 3. Re: How to break lines for NULL values
              Joe Mako

              Version 7 may have what you are looking for, the ability to complete the domain for dates. Have you tried the beta yet?

              • 4. Re: How to break lines for NULL values

                No, I will check the beta out. Thanks!

                • 5. Re: How to break lines for NULL values

                  Yes, version 7 does what I wanted. Great!

                  • 6. Re: How to break lines for NULL values
                    Shane Dayton

                    Could someone elaborate on how to "complete the domain for dates"?  I'm trying to create a line graph where the time component is months (continuous).  The aggregated measure is total hours of overtime.  In some months there was no OT worked and I'd like for these months have a value of zero (instead of just skipping over the month).  Is this possible?

                    • 7. Re: How to break lines for NULL values
                      Jonathan Drummey

                      Hi Shane,

                       

                      Most anything is possible with Tableau, however some things take a lot more effort on the pre-processing side to make them work. In your case, you're probably going to need to give Tableau some data so it can draw a mark for those zero rows. The exact method will depend on how the overtime is calculated. If it's a measure that is calculated outside of Tableau so there really are no rows for the missing months, you could edit your data source to start with a table of months and left-join the rest of your data/query to that, so there is at least one row for each month (which would have NULL values for all the other fields). Then you could alter your calculation to return 0 for those months. If you already have hours worked as a set of rows and overtime is a calculated field based on that, it might be possible to do set up the calculation to return 0 for those months and draw the lines correctly. If you need help, I suggest posting a packaged workbook (.twbx) with some sample data.

                       

                      Cheers,

                       

                      Jonathan