6 Replies Latest reply on Feb 27, 2018 8:42 PM by siying.tan.3

    Display only the outliers

    siying.tan.3

      Hi all,

       

      I have box plot chart which will display outliers. I was wondering if we can only display those that have outliers.

       

      Scenario: My database contain customer purchases details across the years. I had plotted a box-plot with my X-Axis as Customers, Y-Axis as Revenue. The box detail is year. I wanted to only display those customers that have outlier points.

      Below is an example of how my chart will look like:

      Outlier Example.PNG

      As Alan & Calvin have outliers, I only want to display Alan & Calvin box plot (ie. excluding Derrick in the chart). As I have a lot of customers, I will not be able to manually removing the non-outliers. Is there a faster way to solve this issue?

      Thanks.

        • 1. Re: Display only the outliers
          Simon Runc

          hi Siying,

           

          So one way we can do this is to use Table Calculations to work out the Whiskers of the Box Plots, and then we can test if a customer has 1 (or more)...

           

          First I had to find out the exact rule Tableau uses for it's Box and Whiskers plot...luckily it's all detailed here https://www.theinformationlab.co.uk/2017/05/03/box-plot-calculations-tableau/

           

          So I created these formulas and set them up as per the below (please note the sort)...they all have the same Table Calculation set up

           

          Once we have this I can test each mark against the Top and Bottom Whiskers

          [Customer Outlier 1/0]

          IF SUM([Sales]) > [IQR: Upper Whisker]

          OR SUM([Sales]) < [IQR: Lower Whisker] THEN 1 ELSE 0 END

           

          and then finally I can use a WINDOW_SUM to find which customers have 1 or more

          [Customer Has Outlier T/F]

          WINDOW_SUM([Customer Outlier 1/0]) >0

           

          This one is just set up like (same as rest, but no need to have the sort...it wouldn't make any difference, but why work harder!)

           

          We can now use this as a filter.

           

          Hope that makes sense, and helps (attached in 10.4)

          • 2. Re: Display only the outliers
            siying.tan.3

            Hi Simon,

             

            Thanks for the help. However, I noticed that it does not work fully.

            For example looking at Sarah Middleton, it gave a different outlier result according to number of customer selected. From your attached file, without changing any item, Sarah Middleton's hinge & whisker calculation does not match the Tableau box plot value. Even though there's no outlier, the outlier condition still return "true". However, when I "Keep Only" Sarah Middleton, the hinge & whisker matches Tableau box plot and it returned "false" for the outlier condition.

            Do you have any idea why this is happening?

             

            Thank you.

            • 3. Re: Display only the outliers
              Simon Runc

              hi Siying,

               

              Apologies...I should have done a bit more checking!

               

              I've had a look and all the calculations need to be set up with this arrangement (you can drag Customer Name to change the order with Month, so you can select it in the Restarting every)

               

               

              Hopefully that does the trick.

              • 4. Re: Display only the outliers
                siying.tan.3

                Hi Simon,

                 

                Thanks for the trick! I'm able to get more accurate results. However, I do noticed that the Upper Whisker is still incorrect for some.

                Example for Joan Oakley Schaefer, her Upper Whisker calculation does not meet Tableau box plot value. However, all her other calculation matches. Similarly, when I "Keep Only" Joan, her Upper Whisker calculation will match.

                Any idea why this is happening?

                 

                Thank you.

                • 5. Re: Display only the outliers
                  Simon Runc

                  hi Siying,

                   

                  Apologies for not getting back sooner

                   

                  ...looks like we're getting closer!

                   

                  So I have slightly amended the Table Calculation set up for the Whiskers, so the Upper and Lower Whiskers are now like this

                   

                  and the Hinges are as before

                   

                  This has been and interesting problem, and I've learned a lot here...which is what the community is all about!

                   

                  If you spot any other anomalies with Tableau vs my Calculation let me know

                  • 6. Re: Display only the outliers
                    siying.tan.3

                    Hi Simon,

                     

                    Sorry for the delay... I was having some issue correcting my own dataset.

                    I don't see any more issue in your attached workbook. Thanks for all the help you rendered. It really helps me a lot!