12 Replies Latest reply on Dec 17, 2014 2:18 PM by Rui Zhang

    Sorting text columns

    Chris Moseley

      I've been reviewing the forums and have seen a number of posts about sorting but none that seem to address my question

       

      I created a table that has a number of text columns and would like to sort the entire table by any of the text columns.   For example, if the columns are - from left to right - Parent Company, Branch Office, City, and Total Sales; Then, sorting by Parent Company works great.  But, if you sort by Branch Office, it will just sort the branch offices within each parent company, rather than putting all the branch offices in alphabetical order, irrespective of parent company. 

       

      A perfect example of the functionality I'm seeking is if you select "view data" in the Tooltip on a graph in Tableau.  Within this view, one can toggle sort any column simply by clicking on the column header.  This strikes me as a pretty common way to manipulate data. 

       

      Is there a way to display my text information in a Tableau Dashboard and retain this ability to sort? 

       

      Just in case this discription was not clear, attached is a sample workbook from some old dummy data I had on my laptop. Specifically, I'm looking to be able to sort by Parent Company or City or Product across all 40 rows on the list on lower left of Dashboard. 

       

      Thanks in advance for your help!

       

      - ctm

        • 1. Re: Sorting text columns
          Ben Porter

          Chris,

           

          See attached workbook.  What you want to do is add a "sale id" as the column (assuming it's a unique id) and then Tableau won't be able to aggregate across dimensions.  In this case I copied the sale amount and converted it into a discrete dimension and hid the header.

           

          Message was edited by: Ben Porter Sorry meant to say add a "sale id" as the FIRST column

          • 2. Re: Sorting text columns
            Chris Moseley

            Thanks Ben, this is helpful, as it looks more like i would like it to but, (unless I'm missing something) it still does not allow me to sort by anything other than the sales.  For example, I can't sort by City or Product.  I want end user to be able to sort by these features without exporting the data.  


            Does that make sense?   

            • 3. Re: Sorting text columns
              Tracy Rodgers

              Hi Chris,

               

              One thing that it looks like Ben started to do was to create a parameter and calculated field based on the different dimension fields. Header1 is a parameter that lists all of these dimensions, the CalcHeader1 uses it in a calculation. By placing the calculation on the Level of Detail shelf, then, right clicking on Sales Amount-->Sort... it's possible to then sort by CalcHeader1 and choose Max (or Min). Click Ok.

               

              Then, right click on the Header1 parameter and select Show Parameter Control. This will allow the user to toggle between how they want the view to be sorted. (I've reattached the workbook)

               

              Hope this helps!

               

              -Tracy

              2 of 2 people found this helpful
              • 4. Re: Sorting text columns
                Ben Porter

                Hi Chris,

                 

                I got a little further.  I added a parameter column heading for the first row so you can choose which dimension you want in the first column (and there by sort by that column).  Drawback is that now you have one duplicated column in your table display.  I'm sure there is a way around this, just can't think of it now. 

                 

                Hope it's helpful

                1 of 1 people found this helpful
                • 5. Re: Sorting text columns
                  Chris Moseley

                  Tracy and Ben,

                   

                  Thanks to both of you.  Very helpful.  I guess this means there is no way to have a toggle sort at the top of each column, which is too bad.  However, this proposed approach provides a reasonable work around, particularly once users know to use the parameter rather than click on the top of the column (which I think will be most people's instinct).

                   

                  Perhaps this is a feature in a future release. 


                  Thanks again. I really appreciate both of you taking the time to work on this!   

                   

                  - ctm

                  1 of 1 people found this helpful
                  • 6. Re: Sorting text columns
                    Chris Moseley

                    Hi Tracy,

                     

                    First of all, can I just say that you are a Rock Star.  I've come across your replies on a number of forums and they are pretty much always helpful. 

                     

                    Anyway, I have a follow-up to the issue you helped with last week (sorting text columns).  So, I've incorporated your work around effectively.  I'd still far prefer the ability to simply toggle sort at the top of each column, like you can on nearly any table on the Web or elsewhere but, this will do for now.

                     

                    However, I noticed that when one of the columns was a date, I got an error in my calculation saying: "Expected type String, found Datetime, results for CASE must match." 

                     

                    This makes some sense so, I created a text version of the date using a  new calculated Dimension with the following logic: "DATENAME('month',[Date Posted / Removed]) + " " + DATENAME('day',[Date Posted / Removed]) + ", " + DATENAME('year',[Date Posted / Removed])"

                     

                    Problem solved, right!?  Well.... not really.  I do get a sortable text column with the dates but they are sorted alphabetically, rather than chronologically. That is, April is first then August, etc.  It doesn't even sort the days in order within a given month in order.

                     

                    Any thoughts on how to incorporate dates into my table and still allow me to sort?


                    Thanks,

                     

                      - ctm

                    • 7. Re: Sorting text columns
                      donna trinh

                      if you hoover your cursor to the lower right corner of each heading of the columns, you should get a bar chart icon to sort it. If you don't and your field/pill (column) is discrete, hoover your curvor cover the right side for the arrow on the pill to show, click on it for the drop down then select sort.

                      • 8. Re: Sorting text columns
                        Don Barnetson

                        Hi Ben or anyone,

                         

                        I have been playing around with the sorting myself, but I have run into a wall trying to sort a list (i.e. Product Sub-Category names)

                        I'm trying to find a way to sort a selected Product so that it would show up on the left side of the bar graph with the rest of the products to the right. It's not sorted alphabetically, or by a measure.

                         

                        My though was to give the selected product the value of 1, using the index() to place it to the left that way by having the index as a range of values.

                         

                        If [Product Sub-Category] = [Product Sub-Category Parameter] then 1 else [Product Sub-Category] end .......etc. or something along those lines.

                         

                        It could be more like a lookup() function, I'm probably just over simplifying the whole thing. I've attached a workbook

                        with something similar to what I'm trying to get...the results aren't quite what I had hoped for...

                         

                        Any suggestions would be greatly appreciated.

                         

                        Thanks,

                        Don

                        • 9. Re: Sorting text columns
                          donna trinh

                          Create a calculated field such as if [region] = 'east' then '4' else if [region] = 'north' then '2' elseif [region] = 'south' then '1' else 'null' end. Drag and drop the dimension of that calculated field to be listed first in the row shelf.

                          • 10. Re: Sorting text columns
                            Rui Zhang

                            Hi Tracy, Ben, or anyone,

                            How did you hide the whole column "Sales Amount" here?Tracy Rodgers

                            • 11. Re: Sorting text columns
                              Tracy Rodgers

                              Hi Rui,

                               

                              Right click on Sales Amount on the rows shelf and un-check "Show Header."

                               

                              Hope this helps!

                               

                              -Tracy

                              • 12. Re: Sorting text columns
                                Rui Zhang

                                Oh, thanks a lot. I assume "Show Header" would only control the header of that column. Wish we could have that functionality