5 Replies Latest reply on Feb 26, 2012 2:55 AM by Jonathan Drummey

    Table calculation to hide rows when null values present

    Rebecca Johnson

      Hi, I'm looking for a calculation which will hide rows in a table with underlying 'null' values - when some of the months for the year selected have data, and some don't, I don't want to show an annual sum.

       

      Attached is what I'm working with. What I'd like is for the industry classifications listed on the annual tab, ones without data in all months (on monthly tab) to be 'hidden'. 

       

      Thanks!

        • 1. Re: Table calculation to hide rows when null values present
          Jonathan Drummey

          I created the following calculated field, there are others that could work (using COUNT() for example) depending on your data:

           

          IF WINDOW_SUM(IF SUM([Retail Sales ($000s)]) > 0 THEN 1 ELSE 0 END) <> SIZE() THEN "Incomplete" ELSE "Complete" END

           

          The inner IF SUM([Retail Sales ($000s)]) statement just generates a 1 if there are sales for a given entry. Then all those results are summed across the table using the WINDOW_SUM() statement, and that is compared to the SIZE() of the partition. That field is then put on the Filter shelf, in the attached workbook I also set it as a Quick Filter so you could see it in action.

           

          Jonathan

          2 of 2 people found this helpful
          • 2. Re: Table calculation to hide rows when null values present
            Rebecca Johnson

            Yes, that wasn't too complicated... then I make the Industry Classification filter global to make my annual table correct... looks good so far.... thanks!

            I've never used <> ; how does that work?

            • 3. Re: Table calculation to hide rows when null values present
              Dimitri.B

              <> means "doesn't equal", i.e.

              IF a <> b

              is the same as

              IF a doesn't equal b

              and is opposite of

              IF a = b

              • 4. Re: Table calculation to hide rows when null values present
                Susan Day

                Hi, I have a similar problem, except that I have multiple columns.  I have applied your solution  which works for me when I have only one column (thank you so much, I had triedall kinds of things before I found your post). 

                 

                I am not sure how to make it work for multiple columns. I also need to hide columns which have null values.

                 

                I have a workbook which uses coffee chain sales, not sure how to attach.  Your solution works if I create a text table with sales for 2011 Oct & Nov for Area codes 203,206,212 & 213 ie it filters out 212 & 213. I also want to add Profit % & calculate Variance $ (diffference in Sales in 2 selected months) & Variance % (diff in profit in 2 selected months)

                 

                The desired output is a text table showing rows for 203 & 206, and columns for

                • October sales & profit
                • November sales, profit, var$ & var%

                 

                Any help would be much appreciated

                • 5. Re: Table calculation to hide rows when null values present
                  Jonathan Drummey

                  Hi Susan,

                   

                  I haven't been able to figure out an automatic or manual way of doing this when there are multiple columns. Joe Mako documented a workaround by creating a calculated field for each column that needs to be displayed (in your case each combination of month and measure that you listed), however this would require manually updating the worksheet each time you wanted to add new months and remove prior months from the display. Here's the workaround:

                   

                  http://community.tableau.com/thread/105639

                   

                  There is a way to use a filter on a table calc to empty columns when only one measure is displayed, see this KB article for details:

                   

                  http://kb.tableausoftware.com/articles/knowledgebase/filter-first-year

                   

                  Here's Tableau's hiding behavior for text tables as I currently understand it:

                   

                  If one instance of a header (i.e. an item of a dimension) is manually hidden, that instance is hidden across all panes. I've always thought this was an example of Tableau trying to make text tables more understandable for the first-time viewer, because variations in # of columns per header can be confusing, instead of the tradeoff for annoying regular viewers who don't want to see column(s) that are always empty.

                   

                  For filters based on a table calc that are used to hide data, if all marks for the field for a given instance of a header in a row or column are filtered out, then hide the header and its parent header, i.e. hide the whole pane that might include other measures, but don't hide any instances of the header in other pane(s). The KB article I referenced seems to describe this behavior, and this is what happens when using a table calc like I described above. When the table calc determines that there is no data for something like var$ for the given month, it not only filters out the var$ it filters out the whole month as well and doesn't show the other measures. I don't know why Tableau does this.

                   

                  Jonathan

                  2 of 2 people found this helpful