8 Replies Latest reply on Apr 25, 2017 7:19 AM by Nagasubramanian S

    Ranking issue

    Nathan Haines

      Hello,

       

      So there seems to be a lot of info on ranking but none of it quite solves the problem i am having.

       

      Essentially all i want to do is rank a whole load of names by the number of sales they have made - which is the easy part. But when i filter it to look at one name, it always shows that person as #1 - but i would like it to keep the number they were at when it was not filtered. Any ideas?

       

      Thanks in advance!

       

      Nathan

        • 1. Re: Ranking issue
          Donna Coles

          HI Nathan - I struggled to get this to work - see my thread here.. its not really possible 'out of the box' How to show rank for filtered list while retaining rank of unfiltered list

          • 2. Re: Ranking issue
            pooja.gandhi

            Hi Nathan,

             

            Dimension filters in Tableau are applied before the table calculations. So filtering on the raw dimension would mean, Tableau filters that dimension first then executes the table calculation operation (opposite of what you want) which is why it always throws a value of 1, because there is only 1 row returned per customer. To fix this, you can create a calculated field on your dimension like:

             

            LOOKUP(MAX([Customer Name]),0) and use this field in the filter instead of the raw dimension filter (this is a table calculation as well). So tableau just looks at the customer name that is selected in the filter retaining the values from the other table calculation and hence giving the unfiltered rank. Hope this helps!

             

            Here is an article explaining the order of operation in Tableau: Tableau's Order of Operations

             

            7 of 7 people found this helpful
            • 3. Re: Ranking issue
              Donna Coles

              Thanks Pooja - this heped me get to what I was trying to achieve too.. maybe I didn't explain myself very well before!

              • 4. Re: Ranking issue
                pooja.gandhi

                Glad that helped, Donna!

                • 5. Re: Ranking issue
                  Lynne Sciandro

                  Hi Pooja,

                  Is there a way to have this apply across multiple worksheets on a dashboard? I would like to only have to select the filter once.

                  Thank you.

                  Lynne

                  • 6. Re: Ranking issue
                    Michael Wray

                    I had the same issue, where i needed to filter across multiple worksheets.  To do this I used the method for filtering using a parameter, which in my case my field was Territory Name instead of Customer Name.

                     

                    You do this by creating a new string parameter, mine was named "Select Territory."  The values are loaded with my territory names, and then I added a new option "All" to show all records.

                     

                    Then I created a new calculated field named "Set Select Territory" with the following definition:

                    ([Territory Name] = [Select Territory]) OR [Select Territory] = "All"

                     

                    Then you add this to your filter, and filter the worksheet where this is True.  Note, you have to create this calculated field on each data source.


                    I found when I wanted to start viewing my ranks by individual territories, that all the table ranks went to 1.  So, I modified my existing parameter calculated field "Set Select Territory" to use the lookup(max()) shown above, and this worked!

                     

                    The new formula looked like this:

                    (LOOKUP(Max([Territory Name]),0) = [Select Territory]) OR [Select Territory] = "All"

                     

                    • 7. Re: Ranking issue
                      Kevin Ayers

                      Bringing this thread back from the dead as I have a similar (I believe) problem.

                       

                      I have a list of words that I need turned into a WordCloud, which is easy enough. Each word is associated with a question number so that I can see the words most commonly associated with Question 1, Question 2, or Question 3. Once filtered to Question 1 I would like to limit the output to only the top X words, say 30.

                       

                      I tried creating the calculated field as Pooja Gandhi shows above but the calculated field as stubbornly a measure, not a dimension, and won't be converted.

                       

                      I'm sure that I'm missing some basic step but I can't figure it out for the life of me. Any help is greatly appreciated!

                       

                      I've attached a file of sample data.

                      • 8. Re: Ranking issue
                        Nagasubramanian S

                        Hello,

                         

                        I am getting error as like this, If I try to do the same.

                         

                         

                        Error message is " Argument to MAX (an aggregate function) is already an aggregation, and cannot be further aggregated."

                         

                        Can you please help to resolve this. Because in the dashboard the ranking is aligned to 1 automatically, to any customer being selected.

                         

                        Thank you,

                         

                        --Naga