4 Replies Latest reply on Jan 10, 2013 8:26 AM by Toby Erkson

    Help with parameter to exclude aggregates that equal zero.

    Toby Erkson

      In the "Workbook_Aging" tab I have a parameter in the upper right corner.  The X-axis is workbook names and the Y-axis is the number of months since they were last viewed.  When the "Yes" radio button is clicked I would like the viz to supress (completely exclude, don't show 'em, not visible, etc.) the items along the X-axis that equal zero in the Y-axis.  Thus the workbook names along the X-axis that have zero "Months since last viewed" would not display in the viz.

       

      What do I need to do?

        • 1. Re: Help with parameter to exclude aggregates that equal zero.
          Joshua Milligan

          Toby,

           

          Here's my solution.  I created a calculated field like this:

          [Months since last viewed] +

          IF [Exclude newly viewed workbooks] = "No" THEN 1 ELSE 0 END

           

          I made it continuous and added it to the filter shelf with the condition that it had to evaluate to at least 1.

           

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Help with parameter to exclude aggregates that equal zero.
            Nathan Krisanski

            Create a calculated field with this formula:

            if [Exclude newly viewed workbooks] = "Yes" and [Months since last viewed] = 0 then "Exclude" else "Show" end

            Add this to your columns shelf (ahead of workbook name). This will split up the workbooks into two panes when you change your parameter to Yes.

            sshot21.jpg

            Now right click on the Exclude label on your view and select Hide. Then you can clean up the header by right clicking on the FilterParam calculated field on your column shelf and deselect "Show Header".

             

            This uses the hide feature of tableau instead of using a filter because the "months since last view" is a window function and wont add to your filter shelf. Has the same effect though. Note these hidden workbooks will be included in any totals or table calcs you do on the workbook aswell. Just for future reference.


            Cheers

            • 3. Re: Help with parameter to exclude aggregates that equal zero.
              Joshua Milligan

              Nathan,

               

              I was able to add it to the filters in my example.  The trick is that it has to be continuous instead of discrete.

               

              I like your solution quite a bit too!  Often, I would try to avoid hiding something as there is no visual indication within Tableau that something is hidden.  At least with the filters shelf I can see that there is a filter in play.  However, the decision on which way to go, in this case, would depend on whether or not those values should be included in totals and table calcs.

               

              Joshua

              • 4. Re: Help with parameter to exclude aggregates that equal zero.
                Toby Erkson

                Wow, nice work guys! 

                 

                I did both and they work.  The underlying data both return is the same.  Joshua's solution was the route I was attempting but the slick solution of Nathan's is, well, slick!  So BOTH are the correct answer!

                 

                I chose Joshua's solution for my workbook is because it is like a coded solution and since I'm a programmer it makes more sense to me.  Nathan's has the advantage that the header can be customized through the calculated field ("Exclude" and "Show" become pane headers when using his method) so that is why I marked it as the Correct Answer.  It was really splitting hairs on choosing.