2 Replies Latest reply on Dec 12, 2016 6:54 AM by Daniel Higgins

    Max rank by dimesion

    Daniel Higgins

      Good afternoon,

       

      I have a list of Organisations and Regions in my data and have calculated various rankings in SQL (quick example below). What I want to do in Tableau is to apply a maximum rank.

       

      For example, if I filter on Manchester, I want Tableau to do:

      National Rank 1 out of 10

      Region Rank 1 out of 6

      Sub Region Rank 1 out of 3

       

      If I put max(Sub Region Rank) is displays as 1 out of 4, as it takes the maximum rank overall, which I understand, I just can't figure out how to partition this in Tableau so that it looks at the max rank per sub region.

       

      Any ideas?

       

       

      OrgSub RegionRegionNational Rank Region RankSub Region Rank
      ManchesterGreater ManchesterNorth West111
      SalfordGreater ManchesterNorth West222
      RochdaleGreater ManchesterNorth West333
      CreweCheshireNorth West441
      CongletonCheshireNorth West552
      StockportCheshireNorth West663
      BradfordYorkshireYorkshire and the Humber711
      DoncasterYorkshireYorkshire and the Humber822
      SheffieldYorkshireYorkshire and the Humber933
      RotherhamYorkshireYorkshire and the Humber1044

       

      Cheers,

      Danny.

        • 1. Re: Max rank by dimesion
          Norbert Maijoor

          Hi Daniel,

           

          Find my approach as reference below and stored in attached workbook version 9.3

           

          • 2. Re: Max rank by dimesion
            Daniel Higgins

            Hi Norbert,

             

            Thanks for the reply. I probably didn't explain myself properly, my fault!

             

            So I have calculated all my rankings in SQL and connected to Tableau. I've created a dashboard, image below, can't upload anything due to the data it's connected to.

             

            As you can see, the Regional Rank is showing 15 out of: 23. The Trust I have selected from the filter falls within Cheshire and Merseyside and therefore I want it to read 15 out of: 20.

             

            I am using max(regional rank) which is why it's showing 23. I understand that. I just can't seem to find a way to say, if Trust A falls within Cheshire then do max rank by the region it falls within, rather than the whole lot.

             

            Does that make sense?

             

            Cheers,

            Danny.

             

            example.png