6 Replies Latest reply on Jan 10, 2014 9:07 AM by Ville Tyrväinen

    Avoid re-ranking when using a select/filter action

    Brandon Maslar

      Hi all,

       

      I've read this forum countless times and found very useful solutions, but I'm stumped on my most recent project. I am trying to avoid having Tableau "re-rank" my data when I select a subset of the data in a dashboard action, is this possible? The only thing I could think of was to "lock" or "freeze" my rankings and export the rankings with all of the data selected and then join the data to its own rankings to identify what it's original ranking was, but that's sort of a one-off work around that would not be dynamic if my data were updated.

       

      Does anyone have any ideas on how to avoid the "re-ranking" of my selected subset of data? I've created a very small sample mock-up data set and dashboard with an explanation below.


      Thanks!

       

      ------------------------------------------

       

      I've attached a mocked up sample data set of 20 customers' revenue and margins over a 4-quarter period, and I've mapped their total annual performance on a Revenue Rank vs. Margin Rank scatter plot (I did this using the INDEX() function and computed grouping on the customer name sorted by revenue/margin descending). For example, if I wanted to identify what was wrong with "Customer 1" (top right of the plot), I could see in the table that it was 20th in revenue, and 16th in margin for the year, and the quarterly detail shows the trend that it was actually #8 in the first half of the year before falling off the second half of the year...

       

      cust1.jpg

       

      My problem is, if I wanted to isolate the poor performers at the top right of the scatter plot, rather than keep their true rankings relative to the entire population(#15 through #20), Tableau automatically re-ranks them relative to the subset (#1 through #5), which I do not want it to do:

       

      cust1_pt2.jpg

       

      Is there a way to lock/freeze the rankings as is? I would prefer to analyze the poor performers relative to all of their peers, not the subset; so ideally, the screen above would keep the original rankings (#20 and #16), and not "re-rank" (#5 and #2). Even worse, whenever I select just one customer to analyze their financial metrics, it re-ranks it relative to itself, so it always has ranking #1 of 1, which is of no use to me. I'd like to be able to isolate a poor performing customer and see how they rank each quarter relative to peers. Thoughts?

        • 1. Re: Avoid re-ranking when using a select/filter action
          Ramon Martinez

          Hi Brandon,

           

          Thank you for the detailed description of your scenario and requirement.

           

          My recommendation for you is just to use select/highlight action instead of select/filter action.

           

          Take a look at you own workbook (attached) with the highlight dashboard action and let me know if this solve your requirement.

           

          Best

          Ramon

          1 of 1 people found this helpful
          • 2. Re: Avoid re-ranking when using a select/filter action
            Brandon Maslar

            Hi Ramon,


            Thank you very much for your response. Your suggestion works well for this very small sample set I created of customers; unfortunately, my real data set has about 4,000+ customers, so highlighting a group in a table that large is not always intuitive or easy to find. I will incorporate your suggestion into my report, but in my real version, since the data set is so large, I actually have an action that launches an entirely new "tear sheet" dashboard for just the selected customers, so I don't think highlighting a group of 100 customers out of a 4,000 customer list will be ideal or necessarily easy to find.

             

            It doesn't sound like avoiding this re-rank this is easily doable, so I might create a temporary work-around for now.

             

            Thank you very much for the input,

             

            Brandon

            • 3. Re: Avoid re-ranking when using a select/filter action
              Joshua Milligan

              Brandon,

               

              One approach might be to rank the data at the source.  What is your source?  Some databases such as SQL Server have a Rank function that you could use as part of a custom SQL statement.  When you created an extract, the result would be materialized and you would have a consistent rank for every customer (you could also use a context filter to ensure a live connection would have the correct rankings).  In Excel you could create calculated columns to rank the customers. 

               

              These are similar approaches to the one you mentioned of "freezing" the values in Tableau, but the benefit is that it would be a one time setup and then you'd get correct values without manual effort when the data refreshed.

               

              Regards,

              Joshua

              • 4. Re: Avoid re-ranking when using a select/filter action
                Ville Tyrväinen

                5 min solution..

                 

                Just tested can it be done without going outside Tableau :-]

                 

                Ville

                • 5. Re: Avoid re-ranking when using a select/filter action
                  Brandon Maslar

                  Hi Ville,

                   

                  Thank you for your response. I believe we ended up getting to the same solution independently. In order to "freeze" or "lock" the rankings, I basically created and extracted separate ranking tables and re-connected the data back to itself based on customer names, is that what you did? It looks like we got the same answer. This will certainly work for my issue, but it will not be dynamic per se, as it will require an update and refreshed extract any time the data or parameters change.

                   

                  Thanks all for the input!

                   

                  Brandon

                  • 6. Re: Avoid re-ranking when using a select/filter action
                    Ville Tyrväinen

                    Hi Brandon

                     

                    You can see detailed info on the last worksheet. Just created couple worksheets which can be just copypasted as crosstabs back to Tableau. Not dynamic solution but takes only couple minutes to be done. But not optimal for production enviroment.

                     

                    Ville