7 Replies Latest reply on Sep 15, 2016 5:29 AM by Sudheesh Rao

    Excluding top 5 values using calculated fields

    Sudheesh Rao

      Hi,

       

      Not sure if this has been covered anywhere since I couldn't find it. I am calculating average discount in each product category but I want to exclude the top 5 orders with the highest discount in each category while calculating the average.

       

      For example in the attached excel sheet. for product 'Bed', I do not want order ID 125568, 125573, 125578, 125583 and 125588 to be considered for the average discount offered for Beds.

       

       

      Is there a way of doing this using calculated fields or sets?

       

      Regards,

       

      Sudheesh

       

       

      Jonathan Drummey@

        • 1. Re: Excluding top 5 values using calculated fields
          David Li

          Hi Sudheesh! I think that there are a number of ways of doing this, but the easiest way is probably to use sets. Just set up a set that finds the top 5 Order IDs by discount.

           

          Then, right-click and hold on the set you've just created and drag it into filters. Pick In/Out from the options, and filter it to only include "Out". That should do it.

          • 2. Re: Excluding top 5 values using calculated fields
            sushma.poduturi

            Hi.

             

            Based on the sorting order, you can use either first() or last () function in your calculation.

            Please see the attached file.

            • 3. Re: Excluding top 5 values using calculated fields
              Jonathan Drummey

              Hi Sudheesh,

               

              @David's approach will work when there is a context filter on the Product Category that only includes a single category, otherwise the Top N could end up including product IDs for other products. Tableau's Top N filters on dimensions do not currently act as a "Top N for each".

               

              @Sushma's approach is heading in the right direction but needs a little more work, I used his workbook to set up the following. If you want to show the average for each category with multiple categories in the view then you'll need to use a table calculation. In the attached I set up an RU Discount field with the formula RANK_UNIQUE(SUM([Discount])) with a compute using on Order ID so that it ranks each order ID based on the Discount (to show you what the rank unique is doing), then WINDOW_AVG(IF RANK_UNIQUE(SUM([Discount])) > 5 THEN SUM([Discount]) END) that also has a Compute Using on Order ID gets the WINDOW_AVG across the remaining rows.

               

              Finally because it's somewhat likely that you'll just want one result per Category I created one more calc that has the formula IF FIRST()==0 THEN WINDOW_AVG(IF RANK_UNIQUE(SUM([Discount])) > 5 THEN SUM([Discount]) END) END that also has a Compute Using on Order ID, this returns a single result.

               

              Screen Shot 2016-08-22 at 8.01.56 PM.png

               

              Then in the final view put Order ID on the Level of Detail and a copy of the table calc on the Filters Shelf filtering for non-Null values to get rid of the Order IDs that we need for the table calc to work:

              Screen Shot 2016-08-22 at 8.02.02 PM.png

               

              This is essentially a variation on a TRIMMEAN() function, I recently wrote a post on it at TRIMMEAN() in Tableau | Drawing with Numbers.

               

              v9.3 workbook is attached.

               

              Jonathan

              2 of 2 people found this helpful
              • 4. Re: Excluding top 5 values using calculated fields
                Sudheesh Rao

                Jonathan Drummey... You are the best JD. This worked perfectly.

                 

                Regards,

                 

                Sudheesh Rao

                • 5. Re: Excluding top 5 values using calculated fields
                  Sudheesh Rao

                  Jonathan Drummey

                   

                  What should be my approach, if instead of constant of top 5, i want to do it by percentile.. say.. exclude the top 5th percentile and perform average calculation on rest of the population?

                   

                  I tried applying the logic from your trim mean blog but it is not giving me the results I am expecting.

                   

                  Regards,

                   

                  Sudheesh Rao

                  • 6. Re: Excluding top 5 values using calculated fields
                    Jonathan Drummey

                    Hi Sudheesh,

                     

                    Given the multiple steps involved I can't diagnose your problem without a lot more detail. The best would be for you to post a Tableau packaged workbook with some sample data and your efforts so far along with the results you are expecting.

                     

                    Sent from my iPhone

                    • 7. Re: Excluding top 5 values using calculated fields
                      Sudheesh Rao

                      Hi Jonathan,

                       

                      Sorry for the delayed response. My HDD crashed and lost all my work. Took some time to set back up everything from backup.

                       

                      I actually wanted to try and do it myself and then come here if I couldn't. I managed to do it by understanding the logic and sense in your TRIMMEAN() in Tableau | Drawing with Numbers which I was not applying correctly before. Brilliant technique and detailed explanation helped me achieve this.

                       

                      I was trying to do a cluster analysis on discounting and I wanted to remove the discount outlers and then get the average of the data set. For Visualization I created box and whisker to show the spread of the data points. In addition, I used parameter control to give option to the user to choose the Upper and Lower Percentiles so they can select what percentile needs to be excluded.Thanks  a ton again Jonathan Drummey