2 Replies Latest reply on Jul 21, 2019 6:01 PM by Shazal Gomes

    Filter and keep only empty rows

    Shazal Gomes

      Hi, i'm trying to filter out the empty rows and just display that line on a text table and i'm having difficulties

       

      I've tried the IFNULL function and then try to filter on the empty row value but in the filter dialogue, the value for empty rows don't show up:

      IFNULL(SUM([Value]),123)

       

      My workbook is attached, essentially i would like to only show the line 2019-august-category 18

       

      Use case: show which items were not sold during the month

       

      The underlying data does not have this item sold for a given month, so I'd like to show it, the requirements are to show it in a text box and isolate it, not show it in different charts or along with the others

       

      Please let me know if you can assist

       

      Thanks

       

      Image 1.png

        • 1. Re: Filter and keep only empty rows
          Don Wise

          Hello Shazal,

          Perhaps it was a part of the original data, but the last entry for August for Category 18 in your workbook that you attached wasn't there as a valid row in your attached workbook.  So, I'm not sure how Tableau represented a row of data that wasn't in the data based on your screenshot? 

           

          Regardless, I brought out your data and added that row back in and left the value as Null for that category in August.  If you follow the screenshots below you should get the result you're seeking.

           

          HOWEVER, if you're indicating that Tableau should recognize that row of data when it's not there to begin with, then that's not physically possible...

           

          We should be seeing 4 rows of data with one Value being null in your data.  All I was seeing is just the first 3 rows in your workbook.

          Screen Shot 2019-07-21 at 7.59.08 AM.png

          Using your IFNULL calculation, you'd get the following when placed on Marks Card:

          Screen Shot 2019-07-21 at 8.00.59 AM.png

          Drag your Calculation to Filters and set it to use maximum value of 123 to see which date/category where/when a product wasn't sold:

          Screen Shot 2019-07-21 at 8.02.31 AM.png

          • 2. Re: Filter and keep only empty rows
            Shazal Gomes

            Hi, that's correct, the dataset will not have this row to start with, this is where the difficulty lies, I could somehow tweak the SQL behind the dataset to add in the row I guess but I really didn't want to have to do that and show users how to be in "self-service" mode and get it done without IT intervention

             

            How Tableau displays the "empty row" is in the following option:

            • Analysis menu
            • Table layout
            • Show empty rows
            • Show empty columns

             

            Image 3.png

             

             

            Given this I can understand the behaviour for Tableau not being able to filter what is physically not there, but I would have some kind of wish that it could since Tableau is able to detect an empty row and even assign a physical display value when a calculated measure is created

             

            Is there any way that you can think of to try to "blend" the same source ? i'll try to explain what's in my mind:

             

            Take source 1 and connect to the full dataset

            Take source 1 again and create a new dataset, but this time hide all columns except the category column so to get the "list" of all possible category values

            Somehow "blend" the data and use category as the "main" information and filter on a null value (with a calculated measure or something)

             

            Just a thought, but if it's not possible, then it's not...i'll have to tweak the underlying dataset somehow with the users

             

            Thank you for replying!