4 Replies Latest reply on Jul 13, 2016 5:07 PM by swaroop.gantela

    Multiple ranks for hierarchical categories

    dan.horner

      I'm hoping someone can help me out; I've been struggling a lot over the last week trying to find an answer in the forums that doesn't go beyond my beginner/intermediate Tableau knowledge.

       

      My revenue data is split into three levels, examples below:

       

      • Continent (e.g. North America, South America, Europe) >
        • Country (e.g. UK, Spain, Canada, France, Italy) >
          • Product (e.g. Car, Bike, Plane, Scooter, Unicycle).
            • $ Revenue

       

      For each of the regions, I want to

       

      1. Rank and filter the top 3 Countries (by their revenue sub-total)

      then

      2. Rank and filter the top 3 products by revenue for each of the top three ranked/filtered countries (and still show the revenue per product).

       

      I need to have Quick filters for both ranks, so I can make this analysis available within my org.

       

      This doesn't feel like it should be overly complicated, but I'm completely stumped. If anyone can provide step by step instructions, I would be eternally grateful.

        • 1. Re: Multiple ranks for hierarchical categories
          swaroop.gantela

          Dan,

           

          I'm not sure if I quite got there, but maybe the attached could be a first step.

           

          I made a worksheet with Continent, Country, and Product.

           

          I created a Rank of the Product Revenue with:

          RANK(SUM([Revenue]),'desc')

          and edited the Table Calculation to have Partitions of Continent, Country, Product,

          At the deepest level, restarting every Country.

           

          I also sorted the Products by descending Sum(Revenue).

           

          I then used a Level of Detail calculation to get the Total Country Revenue:

          { FIXED [Country]:SUM([Revenue])}

           

          I sorted the Countries by descending [CountryRevenue].


          I created a calculation of simply INDEX() and placed that on the worksheet.

          I edited the Table Calculation to have Partitions of Continent, Country, Product,

          At the of [Country], restarting every [Continent].

           

          I was then able to put both [ProductRank] and [Index]

          on the filter shelf and just select choice 1, 2, and 3.

           

          210457rank.png

          • 2. Re: Multiple ranks for hierarchical categories
            Kriti Srivastava

            Hey I have the same kind of question.

             

             

            I have a Main Application: XYZ under this Main Application I have multiple Application ID say A,B,C,D,E,F. Now under these Application IDs I have multiple Function IDs say  App ID A has 1,2,3,4,5,6,7: App ID B has 7,8,9,0 and so on.

             

            Now what I need is Rank the Application ID and Rank the Function IDs under those Application IDs by a count if usernames. Till now what I have achieved is only the ranking of Function IDs.

             

            Desperatelty need help!!

             

            I tried your method but it isn't working.

            • 3. Re: Multiple ranks for hierarchical categories
              Sreenadha Reddy Palakolanu

              Hi Dan,

               

                Please find the .twbx file.

               

              Regards,

              Sreenadha Reddy Palakolanu

              • 4. Re: Multiple ranks for hierarchical categories
                swaroop.gantela

                Kriti,

                 

                Please see if the attached could be a first step for you.

                 

                First, I calculated counts of UserNames at each level (Function,App,Main)

                using Level of Detail calcuations. For example [CountFunctionUsers] is :

                { FIXED [Main App],[App ID],[Function ID]:COUNT([Username])}

                and [CountAppUsers] is:

                { FIXED [Main App],[App ID]:COUNT([Username])}

                 

                Then used the technique described here to make combined fields:

                Nested Sorting | Tableau Software

                 

                For example, I combined AppID and FunctionID

                and put that on Rows shelf of Sheet2. I then sorted that by

                descending and SUM(CountFunctionUser).

                 

                Likewise, this can be done for Main and App.

                 

                The Combined field can be hidden from the view

                by unselecting "Show header"

                 

                210457app.png