1 2 Previous Next 27 Replies Latest reply on Apr 10, 2011 11:11 PM by . Dan

    Sorting color legends

    . Dan

      I use Tableau to track share prices indexed against a date eg. performance since the start of 2011 and so on. (The share price data consists of a daily time series for each of multiple stocks.)

       

      I like to have the entries in the colour legend sorted according to the performance of the stocks in the pane or sheet for reasons of clarity. However, the only way to do this seems to be manually drag and drop the colour legend items, which is a lot of work for 2-3 dozen stocks in a 4-pane dashboard, especially when performance changes day to day.

       

      Has anybody found a way around this so that color legends can be sorted automatically according to, say, the value of the field?

       

      Thanks

      Dan

        • 1. Re: Sorting color legends
          Richard Leeke

          You should just be able to sort the dimension that is on the color shelf (either by clicking on the drop-down menu on the dimension on the color shelf or from the drop-down on the color legend header) and selecting the sort order based on the field you want.

           

          I do that all the time and it usually works - but just today I've been doing battle with a case where it wasn't giving me what I wanted.  I suspect that had to do with filters or something - the sort order may have been including rows that were excluded from the view, or something like that - I didn't have time to look properly.

          • 2. Re: Sorting color legends
            . Dan

            Hi Richard, thank you for taking the time to comment. I have "Measure Names" on the colour shelf. Clicking on the dropdown and then on the Sort button gives me the choice of sorting by data source order, alphabetic or manual.

             

            Unfortunately none of those do what I want. Ideally I would like an option to sort by the most recent value in the rows, but I can't see any way to do that. As an example, if I am indexing performance from the start of 2011 and IBM's share price has risen by 5%, Apple's by 10%, Microsoft by 20% and Cisco's by 18%, the sort order of the items in the colour legend based on those values would be Microsoft, Cisco, Apple, IBM.

             

            Dan

            • 3. Re: Sorting color legends
              Richard Leeke

              OK I see.  So I take it IBM, Apple, Cisco and Microsoft are columns in your datasource?

               

              The only way I can think of would be to unpivot your data, so that you had [Stock] as a dimension which could go on the Color shelf.  Even then, I don't know any way of sorting the legend by the latest entry in a time series - though I can see why that would be useful.  Not saying you can't - just that I don't know how to.  Joe or Andy or someone may know a cunning trick.

              • 4. Re: Sorting color legends
                . Dan

                "OK I see. So I take it IBM, Apple, Cisco and Microsoft are columns in your datasource?"

                 

                That's right. The source is an Excel spreadsheet with a daily date in column A and dozens of stocks from column B onward.

                 

                Dan

                • 5. Re: Sorting color legends
                  Andy Cotgreave

                  Hmmm. I thought I had this solved with this workbook on Tableau Public but it doesn't work out the correct maximum value. The colour legends are being sorted by the maximum value in the range of dates, not the value in the last date in the series. Joe's the man to do the final workings on this, I reckon!

                  • 6. Re: Sorting color legends
                    Joe Mako

                    I agree with Richard, you will need to "unpivot" your data before connecting Tableau to your data, see http://www.tableausoftware.com/support/knowledge-base/preparing-excel-files-analysis for an explanation, examples, and an Excel add-in.

                     

                    Then just to clarify, are you getting your percent difference before Tableau, or is it calculated with a table calc in Tableau? Do you want the sort of the legend to use the last (most recent) value in the data source, or the last value in the the portion of data currently displayed?

                    • 7. Re: Sorting color legends
                      . Dan

                      Thanks to Joe and Andy.

                       

                      Joe - I am calculating the percent difference in Tableau as a table calc (see attached screenshot).

                       

                      Typically I will filter the data from the beginning of the year so that I can see year-to-date performance. In this case, I am simply using the most recent value in the data source. That is, the final value in the displayed data is also the most recent value in the source data so I would want to sort on that value.

                       

                      However, I might sometimes want to specify the beginning AND end dates e.g. from the beginning of 2010 to the end of 2010, or similar. So in those cases I would be want to sort on the last value in the portion of data currently displayed.

                       

                      Also, I had thought my data was pretty clean and simple - it's regular in shape and contiguous within its boundaries. One row as a header, then daily data. When yourself and Richard suggest unpivoting, does that mean putting it all in rows in the spreadsheet form of date,stock_ID,value or perhaps stock_ID,date,value?

                       

                      I apologise for not supplying a packaged workbook but the data used comes from a third party vendor and I do not have the right to redistribute.

                       

                      Thanks again

                      Dan

                      • 8. Re: Sorting color legends
                        Joe Mako

                        I grabbed some data, and made the attached.

                         

                        Is this what you are looking for? If so, I can go into more detail.

                        • 9. Re: Sorting color legends
                          . Dan

                          Wow, consider me suitably awed. That seems to work very well indeed! I've had a look at it but can't quite work out how.....? And the raw data formatting would be a bit different...?

                           

                          [Edit] Is there any reason why this should not work with multiple (e.g. 4) panes per dashboard, each with its own legend and using a common date?

                           

                          Many thanks for the education

                          Dan

                          • 10. Re: Sorting color legends
                            Joe Mako

                            Here is a quick 5 min video on how I did it:

                            http://joemako.tumblr.com/post/3734611805/creating-a-worksheet-to-look-like-a-legend-and

                             

                            You may want to watch full screen and/or with HD on. You can also download the original .mp4 from the Vimeo link.

                            • 11. Re: Sorting color legends
                              Joe Mako

                              In regards to: "Is there any reason why this should not work with multiple (e.g. 4) panes per dashboard, each with its own legend and using a common date?"

                               

                              Are you using a global filter? or can you modify the workbook I attached to reproduce your situation?

                              • 12. Re: Sorting color legends
                                . Dan

                                Joe, first of all, I really appreciate the video. I actually learned several discrete things from that, unrelated to the question in hand. Second, on the "is there any reason it should not work" I have not yet tried, but just wondered if there was anything inherent to the structure that might make it hard.

                                 

                                Depivoting my data would be nasty, but not impossible and it would have certain other benefits as well. Having just used the reshaping add-in and found it easy but rather slow, I suspect that dumping the entire sheet to a text file and throwing a Perl script at it would be significantly quicker.

                                 

                                Whatever, for now, thank you again. I will experiment over the next few days with multiple sheets etc.

                                 

                                Regards

                                Dan

                                • 13. Re: Sorting color legends
                                  Andy Cotgreave

                                  Joe, that's very very cool. I never thought of using Shape as a way of emulating the look-and-feel of a native Colour Legend.

                                  • 14. Re: Sorting color legends
                                    Joe Mako

                                    This approach to creating a legend also allows me merge the Shape and Color legend into one legend when both the color and shape shelf have the same pill.

                                    1 2 Previous Next