7 Replies Latest reply on Mar 28, 2016 10:58 AM by Milton Forde

    Top # Brand Rank not working

    Milton Forde

      Hello!

       

      I have what I think is a unique situation.  I created a top rank filter for my category brands.  Within the brand I have formats that are associated with the brands.  When I pick all formats, my ranking is correct.  But when I deviate to specific formats I loose my ranking number.  For example, top 20 will only give me 19 for bags, 14 for cans but if I choose All, I get all 20.

       

      I've tried modifying my brand calc statement, my Top N range, nothing seems to correct this.   Is there a way to have this work across all formats?

       

      Attached is the workbook that I am working with for reference.

       

      Thank you in advance for your help.

       

      -Milton

        • 1. Re: Top # Brand Rank not working
          Mark Fraser

          Hi Milton

           

          It's been 3 days an no replies...I will try and provide some guidance

          Thank you for providing a sample.

           

          I had a very quick look, and I wonder why you are using INDEX() in place of RANK(), is there a reason?

          The problem with INDEX(), as you are discovering, is that it can't always be used in the same way... it will work without filters, but with filters it wouldn't be suitable for your intended implementation.

           

          If you have access to v9, you may also wish to consider using LOD calcuations

          This document may/ should help >> Multi-Level Sorting with Ranks

           

          I would think about using RANK as well as partitioning and addressing and LODs

          If you used RANK, you could then use that as your filter, rather then using logical statements with parameters.

          A super basic example from superstore

          Rody Zakovich is my go to man for Ranking questions...

           

          Cheers

          Mark

          1 of 1 people found this helpful
          • 2. Re: Top # Brand Rank not working
            Rody Zakovich

            Thanks for the ping Mark.

             

            Milton -

             

            There are a few issues here, but here is how to fix your Viz for the time being.

             

            Step 1. Change the [Rank] calc  from INDEX() => RANK(SUM([Dollars])) -> You could use INDEX, but I prefer using RANK for situations like this

             

            Step2. Change the [Brand] calc to compute using Table Down

             

             

             

            Set to compute using Table Down, so that it is being computed on what is visible in the Viz (After filtering) as opposed to the underyling data

             

            Step 3. Edit the Filter to Exclude NULL Values

             

             

            This eliminates all Brands that are NULL due to the ATTR() calc on Brand.

             

            Now you will always see the TOP 20 (Assuming that there is at least 20 to show [Filter Packs only has 5]])

             

             

            I didn't attach the workbook since I am on a higher version of Tableau, but follow these steps and you should be good.

             

            There is an easier way to set this up, but given the complexity of your viz (Number of Filters, Dims, and calcs), I didn't have time to completely reconstruct it.

             

            Regards,

            Rody

            3 of 3 people found this helpful
            • 3. Re: Top # Brand Rank not working
              Mark Fraser

              Thank you Rody

              Not only for confirming my suspicion regarding INDEX vs RANK but providing such a detailed guide

               

              You have confirmed yourself master of ranks!

               

              @milton, please let us know how you get on, and assuming Rody's answer helped, please mark it correct, its useful for others who may stumble on this thread.

               

              Cheers

              Mark

              1 of 1 people found this helpful
              • 4. Re: Top # Brand Rank not working
                Milton Forde

                Mark,

                 

                Thank you for responding.  For some reason I never received the e-mail through my office mail and logged on to Tableau today to see that you had responded.   Well I'm happy to say I was relieved to see your note and Rody's note as no one was able to help me on this issue.

                 

                The suggestion made by you and detailed by Rody has worked!!!!

                 

                I set it up and used all different scenarios to test it, all with success.

                 

                Thank you very much for helping me out on this one!

                 

                -Milton

                • 5. Re: Top # Brand Rank not working
                  Milton Forde

                  Rody!

                   

                  Thank you for all your help!!   As I mentioned to Mark, your suggestion of setup worked.

                   

                  I appreciate the time you took to help me on this one.

                   

                  -Milton

                  • 6. Re: Top # Brand Rank not working
                    Rody Zakovich

                    Hello Milton,

                     

                    I'm glad to hear that the solution is working for you. If you run into any issues, please let us know.

                     

                    Also, if you could mark a response as correct, that will help other users who are searching for solutions to similar problems.

                     

                    Best regards,

                    Rody

                    1 of 1 people found this helpful
                    • 7. Re: Top # Brand Rank not working
                      Milton Forde

                      Hi Rody,

                       

                      Yes, will do on correct response.   I also have one more question which I hope will be an easy one.  I have my setup complete, but now I would like to add an All Other line to bring in all the brands that are not part of the Top N number.   This is the calculation that I'm using, but it looks to only bring the next line after the Top N line. "IF [In Top N?] then ATTR([Brand Description]) else "ALL OTHER" END.   My ALL OTHER has the following calc statement "[Rank] = [Top N] + 1"

                       

                      Again, thank you for your help.