6 Replies Latest reply on Jun 17, 2016 4:34 AM by Lucie Brett

    Showing top X number using a set

    Lucie Brett

      Hello

       

      I am trying to show the top 5 in a group by their sales however I am only looking at their sales if a particular date field is has a date from July 2015 to May 2016. I have a horizontal stacked bar chart as per the attached and I have used the following site: Create Sets for Top N and Others | Tableau Software.

      I have followed the guidance and created a set with gives an "in" or "out" set however the top 5 by sales are not in the "in" section (Michael should be there instead of Frank).

      What I ideally want to show is only the top 5 - in Excel I would do this in a pivot table by going to more sort options, only showing the top 5 and then creating a pivot chart. I am not interested in the "out" section (in reality I have a list of over 100 names).

      I attach a packaged workbook in case anyone has dealt with this before.

       

      If there is alternative guidance online, please also direct me.

      Thanks so much, Lucie

        • 1. Re: Showing top X number using a set
          khalid norat

          Hi Lucie,

           

          Are you asking how to hide the out section of your visualisation??

           

          If this is it then you just need to right click out and select exclude this will create a filter on the filter shelf and exclude the out section.(workbook attached)

           

          If you are asking for something completely different you will need to elaborate a little more.

          • 2. Re: Showing top X number using a set
            Lucie Brett

            Thanks Khalid, the real issue here is that it isn't the top 5 which is showing. There is an error, like I said above Michael is included in the "out" section. I think maybe the set is not taking into consideration the date filter, could that be it? In which case, can I create a set with a criteria i.e. the top 5 which have a date between X and X?

            • 3. Re: Showing top X number using a set
              Mark Fraser

              Hi Lucie

               

              I had a very quick look - I think the issue is when you include the Month, you then confuse the Ranking.

              I'll try and explain...

               

              For the whole period (all months) you have a total sales, and a total sales per person

              For your period, you have the same, just smaller

              You can quite easily do the top5 for your time period like this

              The problems start, when you reintroduce month...

              Below is an example of what is happening (not exact, but the idea)

               

              You'll hopefully see that each month, now ranks the sales people

              For example Frank is no1 in March, he is no8 in July

              Depending on exactly how you want to display/ calculate will determine where we go next

              Am I right to assume you want the ranking based on total sales (per person) for the selected time period?

               

              Cheers

              Mark

              • 4. Re: Showing top X number using a set
                Lucie Brett

                Thanks Mark, you are right! I am looking at the top X people on a cumulative basis however I need the date as a filter as I don't want to include the sales of records where there isn't a date in the selected time period.

                • 5. Re: Showing top X number using a set
                  Mark Fraser

                  Hi Lucie

                   

                  I'm just off out the door, so I'll be real quick...

                  I created a new field, Sales

                  >> IF [Date Received] >= [Start] and [Date Received] < ([End]+1) THEN [Sales Amount] END

                   

                  And 2 parameters, start and end (they are dates)

                   

                  Now Sales only sums the sales amount between the date we (or another end user) specifies.

                  Now swap Set to work from Sales Amount to Sales.

                  Remove the Month filter (you can leave it but its not required)

                   

                  We should now have the right top 5 people

                   

                   

                  v9.3.3 attached.

                   

                  Any questions let me know and ill try and help

                   

                  Cheers

                  Mark

                  1 of 1 people found this helpful
                  • 6. Re: Showing top X number using a set
                    Lucie Brett

                    Thanks Mark, this has worked like a dream.