11 Replies Latest reply on Nov 30, 2016 10:50 AM by Rekha Kesavan

    Top N By Year

    anthony.pierzchalski

      Hi,

       

      Forgive me as I'm not an expert and it's been a while since I built a workbook.  I searched and searched and may have even asked previously, but going to try again because this seems so simple and is driving me crazy that I can't figure it out or find..

       

      What I want in the attached is the Top 10 sub category for each individual year and keep it in the format I have it currently.  Please help.

       

      Thanks

        • 1. Re: Top N By Year
          Cheryl Grinds

          Hi Anthony,

           

          Based on the workbook you provided, it looks like you just need to modify the current filter so it filters based off the top 10 of sales per sub-category.  I've attached an example using the workbook you provided with the modified filter.

           

          Additionally, this article may prove helpful: Finding the Top N Within a Category | Tableau Software

           

          ~Cheryl

          • 2. Re: Top N By Year
            anthony.pierzchalski

            Hi Cheryl,

             

            In the workbook you attached, if I look at 2014, Binders is sorted incorrectly.  I need the top 10 sorted by Sub Category based on sales for each individual year, not sales across the whole data set.  The workbook has the same Sub Category sort for all the years shown and that's not what I'm looking for.

             

            I've tried clicking on the link you sent, but can't seem to get to the article.  The name of the article looks familiar though and the problem I was having with most of the articles I came across is the Year of Order Date isn't a clean cut dimension by itself to create a combined filed along with Sub Category.

             

            Please let me know if you have any additional questions or ideas for me.

             

            Thanks

            • 3. Re: Top N By Year
              Rekha Kesavan

              Hi Anthony,

               

              Attached is the work book in 10 version. Below are the steps followed to create Top N (Parameter) for sales based on each Year.

               

               

              1. Create a Calculated field using LOD

              2. Create a dynamic set based on parameter input

               

              3. use the set in your viz.

               

               

              4. Finally Top N Sub categories based on Sales per Year. Created Rank Table calculation for sorting.

               

              • 4. Re: Top N By Year
                anthony.pierzchalski

                Hi Rekha,

                 

                I didn't see any workbook attached in your reply?  Either way, I wouldn't be able to open it because the latest version of Tableau Desktop I have is 9.3.  I will look through your email and screenshots for now.  Could you attach the workbook in the earlier version of Tableau that I'm currently running?

                 

                Thanks

                • 5. Re: Top N By Year
                  anthony.pierzchalski

                  Hi Rekha,

                   

                  I downloaded Desktop 10.1 and was able to download the workbook you attached.  Not sure why I was unable to see originally.  Sorry about that.

                   

                  However, when I open your workbook and 10 Top Sub Categories is chosen off to the right, I don't see BINDERS in 2014.  If I select 20 for Top Sub Categories, BINDERS appears in 2014 as #3 with $72,986.  This appears to be in the Top 10, so not sure why it doesn't show when 10 is chosen in Top Sub Categories.  Is the Top 10 actually on the whole data set and not just the year like it needs to be and the level of detail needs to be adjusted?  I have no experience with level of detail, but trying to read up on it.

                   

                  Thanks

                  • 6. Re: Top N By Year
                    Justen Burrhus

                    Hi Anthony,

                     

                    I'm having the same Delima here and I think Rekha is 90% of the way there.

                     

                    Tableau is choosing the Top 10 based on a specific year, 2011 probably, and applying 2011's top 10 data labels to each successive year. Tableau needs to find the top 10 per year independently. I'm a very new user but the only way I can think to solve this(brute force method) is to separate each year into its own data set and then set/force them side by side dropping each year into columns or rows independently.

                     

                    I hope that helps someone figure it out because I'm stumped.

                     

                    Justen

                    • 7. Re: Top N By Year
                      Rekha Kesavan

                      Hello Anthony,

                       

                      I guess all you need is top N Sub Categories within each year based on their sales. If that is so, all we have to do is add a rank for Top N Sub Categories based on Sales.Please see attached and let me know if that is not what you are looking for.

                       

                      1 of 1 people found this helpful
                      • 8. Re: Top N By Year
                        Cheryl Grinds

                        Hi Anthony,

                         

                        It sounds like my initial workbook had the correct data, but wasn't sorting in the way you needed.

                         

                        I added a RANK calculation to my original workbook and used that to filter, is this more what you're looking for?

                         

                        ~Cheryl

                        1 of 1 people found this helpful
                        • 9. Re: Top N By Year
                          anthony.pierzchalski

                          It appears the RANK is what needed to be added.  Thanks Cheryl and Rekha.

                          • 10. Re: Top N By Year
                            anthony.pierzchalski

                            Hi Rekha,

                             

                            Quick follow up, with the RANK, was a level of detail calculation even needed to achieve what I was looking for?  Even if level of detail was needed, how come I couldn't do in version 9.3?  I thought LOD was introduced in level 9.0?  Could this be done in Tableau version 9.3?

                             

                            Again, thanks for you assistance.

                            • 11. Re: Top N By Year
                              Rekha Kesavan

                              Hello Anthony,

                               

                              Thanks for marking the solution as correct. For this we don't need any LOD and the result can achieved using Tableau prior versions. These are table calculations using RANK and then making the "calculated using" as Pane down.

                               

                              Thanks

                              Rekha