5 Replies Latest reply on Jul 27, 2018 5:04 AM by suresh mareedu

    Top-5 Selection issue on Filter based on Ranks

    suresh mareedu

      Hi Team,

       

      I am creating the bar chart with top-5 products based on the rank with respective of region, here I'm having ranks from Rank-1 to 5 (1,2,3,4,5) and dimension is products and created region as filter. here my 1st priority is to show top-5 products based on Rank-5 and then after Rank-4, In case there is no Rank-5 value in any region It has to show the Rank-4 wise Top-5 products with respective of Region Filter.

      I created the top-5 filter by using if condition like Ex: if Rank=5 then [Number of Records] else if Rank=4 then [Number of Records] end

      But I'm getting the wrong values.

      Please can somebody help me on this issue.

      Please find the images which I tried for your reference.

       

      Got the values like below:

      here Red color is Rank -5 and Orange is Rank-4 and count of Ranks.

       

      Actual Data is below:

      As per above Data it should show the Top-5 like below:

      Top-1 Arrow Electronics

      top-2 Hutchinson aerospace

      top-3 servotronics,inc

      top-4 Bmt aerospace-carat

      top-5 norance inc

       

       

      Thanks in Adv's.

       

      Regards,

      Suresh

        • 1. Re: Top-5 Selection issue on Filter based on Ranks
          Steve Martin

          Hi Suresh,

           

          First-off: Please do not share company data in any capacity, even as an image.

           

          On to your question, are you not able to use a rank() calculation such as:

           

          [Rank of Records] Rank([Number of Records],'desc')

           

          Once properly scoped, you will be able to use this to perform both filtering and sorting.

           

          See my attached workbook.

           

          Steve

          • 2. Re: Top-5 Selection issue on Filter based on Ranks
            Okechukwu Ossai

            Hi Suresh,

             

            You mentioned that you are using [Region] as a filter. Do you want an overall rank of the products? Or do you want to rank them within each region? Filtering by Region may be an issue if you want an overall rank. In that case, consider using a LOOKUP filter instead.

             

            LOOKUP(ATTR([Region]),0)

             

            Hope this helps.

            Ossai

            • 3. Re: Top-5 Selection issue on Filter based on Ranks
              Steve Martin

              Ossai,

               

              This is incorrect. When using any table calc, you would need to set the scope as Tableau will default to Table Across or Table Down depending on the data layout.

               

              Your method avoids using a rank so will not actually work.

               

              And finally, check my attached workbook, I have actually covered off both a total rank which ranks everything in the wind and then sorts, and, a pane rank which does the same but at the pane level.

               

              Steve

              • 4. Re: Top-5 Selection issue on Filter based on Ranks
                Okechukwu Ossai

                Steve,

                 

                Maybe, you misread my post. I asked Suresh few questions, so I can understand what the problem is, since no sample data was attached. My suggestion doesn't avoid using a rank. It actually says that he should be mindful of the filters he is using which may affect the Rank output.

                 

                Ossai

                • 5. Re: Top-5 Selection issue on Filter based on Ranks
                  suresh mareedu

                  Hi Steve,

                   

                  Thanks for your your suggestion and It is not a company data, I took some sample data .

                   

                  And  in my requirement I'm not applying any rank condition here, Rank is coming from data only. Here I want to show the combination of Rank 5 & 4 wise Top-5  Products as a stacked bar with respective of region filter(As I attached image in my Post). In this condition my 1st priority is to show the Rank-5 wise Top-5 products and then Rank-4. In case there is no Rank-5 in any region it has to show the Rank-4 wise top products.

                  I tried with multiple calculations but it's calculating only based on count of products but not based on count of Rank-5 & 4.