10 Replies Latest reply on Aug 7, 2013 6:31 AM by Josh Fields

    Is it possible to keep data from being sorted?

    Josh Fields

      Hello All,

       

      I'm trying to build some reports in tableau. I have a column called name which is the name of a sprint. This column does not need to be sorted and should just be in the order that is on the SQL server.  I have a parent table which is joined in Tableau by a parentId column.

      The two tables look like this:

      Team table:

      Id, Name, ParentId, Start Date, ect.(Extra information not used in sorting)

       

      Sprint table:

      Id, Name, Start Date, ect.(Extra information not used in sorting)

       

      The Sprint table is sorted by another program before it is placed in the sql server. So the order of the Team table is based on the order of the Sprint table. I am trying to show a line graph of some information in the Team table which should show a line decreasing from the start of the table to the last record in the table.  Here is an example of what is in the Team table:

       

      Team Table: (Name, Data Used for Line graph)

           Sprint 1 - T1, 100

           Sprint 1 - T2, 100

           Sprint 2 - T1, 80

           Sprint 2 - T2, 80

           QA, 80

           Sprint 3 - T1 - 40

           Sprint 3 - T2 - 40

           QA, 40

           Outsourced Sprint 4, 20

       

      Tableau Orders the items like this:

           Outsourced Sprint 4, 20   

           Sprint 1 - T1, 100

           Sprint 1 - T2, 100

           Sprint 2 - T1, 80

           Sprint 2 - T2, 80

           Sprint 3 - T1 - 40

           Sprint 3 - T2 - 40

           QA, 120 (Combined both QAs)

       

      Is there a way to keep tableau from doing this? I am new to tableau and if you need any more information please ask and many thanks to all those who help.

       

      I have added some sample data with a sample workbook that doing what I am talking about. The first sheet (Wrong) shows what it is currently doing. The second sheet (Almost ...) shows what I need expect the Test QA is still off and I did that by manually sorting the Team Name.

        • 1. Re: Is it possible to keep data from being sorted?
          Joshua Milligan

          Josh,

           

          It's almost never a good idea to rely on the order of records in a table (in any tool or ad-hoc query).  Even if your data source seems to return data in the order in which it appears in the table it's not guaranteed to consistently do it and other databases that Tableau supports may not do it at all.  And then take into account that nearly every query Tableau is sending to the data source is at an aggregate level...

           

          So, as you've seen, Tableau will default to an alphabetic ordering of the field values.

           

          The good news:

           

          You can define the default sort of any field in Tableau.  To do this, right click the field, select "Default Properties" --> "Sort..."  In this case, you will probably want to manually sort the items.  Alternatively, you could provide an "Order" field and use that field to define the sort -- but with a few, static items manual sorting is a good option.

           

          Hope that helps!

           

          Regards,

          Joshua

          • 2. Re: Is it possible to keep data from being sorted?
            Joshua Milligan

            Josh,

             

            I also realize that my answer is very generic and your question is more specific and likely requires more than just a manual sort.  It would help me to give a more specific answer if you could provide a packaged workbook with some sample data and the visualization on which you are working. 

             

            Welcome to the forums!  Take a look at this document to help you get started:So Your Question Didn't Get Answered...

            • 3. Re: Is it possible to keep data from being sorted?
              Josh Fields

              Joshua,

               

              Thanks for the replay. I would normally agree that you cannot relay on the order of the records because you don't have control over where the data is coming from. But I am also working on where the data is coming from and I had to put the data in a certain order to do certain calculations before I put it in the SQL server. So I can always guarantee that the data will be in the order I want. I have done a manual sort for now but I cannot use that as a permanent solution because I have 200 records to check the sorting right now and that number will grow. I will work on trying to get you a sample of what I'm doing.

              • 4. Re: Is it possible to keep data from being sorted?
                Joshua Milligan

                Josh,

                 

                I suspected that would be the case.  I'll be happy to take a look at the sample workbook when you get it ready!

                 

                --Joshua

                • 5. Re: Is it possible to keep data from being sorted?
                  Josh Fields

                  Joshua and anyone else reading this,

                   

                  I posted a sample workbook and some sample data on the original post. Let me know if you have any question or if I need to make anything else clear.

                   

                  Thanks for all the help.

                  • 6. Re: Re: Is it possible to keep data from being sorted?
                    russell.spangler

                    Test QA is still a problem, because it is being aggregated based on Team Name alone.  So Tableau combines the 3 "Test QA" values into one on your chart.  An easy fix is to use a RowID (aka an identity field in SQL), which allows separation of all rows (and you can order by it also).  You can hide the header in Tableau if you don't want to see RowID.  You can achieve the same effect by editing the values, and making "Test QA" different from each other such as Sprint 3 - Test QA and Sprint 4 - Test QA, or you can also drag Sprint Name in to the column area, which would then separate your data based on the combination of Sprint Name & Team Name (which are unique values).


                    Just my suggestions, really depends on what final view you want.

                    • 7. Re: Is it possible to keep data from being sorted?
                      Josh Fields

                      Russell,

                       

                      Thanks for the replay. That is actually really helpful and almost gives me the results I need.  The data now shows up the way I need it but the line graph is now just dots. Is there a way to connect them or will I just have to deal with it?

                       

                      Thanks,

                      Josh

                      • 8. Re: Re: Is it possible to keep data from being sorted?
                        russell.spangler

                        There is, by doing some of the other techniques I was talking about. In short, the data needs to be changed to create a unique value.  You can do this multiple ways, but I would suggest doing it up front in the data.  See attached, there are different dimension now, so you can do "slices" by Sprint Name, Team Names, and the combination of Sprint and Team Name, this will provide more flexibility in your reporting.

                         

                        You mentioned SQL in your post, think of it as doing Group Bys in SQL and how data can be aggregated differently as you add columns in your query.

                        1 of 1 people found this helpful
                        • 9. Re: Re: Is it possible to keep data from being sorted?
                          Joshua Milligan

                          Russell,

                          Good suggestion and explanation!

                           

                          Josh,

                          The reason you are getting a dot is that Tableau does not connect lines across two or more levels of headers.  To get around this, you could use a combined field to combine the RowID (Row Number in the attached workbook) and Team Name.  That will give you a single field for a single header that retains the order and separation you want.  (To create a combined field, select two or more fields -- hold CTL and click -- and then right click and select "Combine Fields").

                           

                          The downside is the default display of the combined field is something like "1, Sprint 1 - T1".  This can be fixed by doing a couple of things:

                          1. Right click the Row Number field and select "Default Properties" --> "Number Format" and use Custom with a single space.  This will hide the number.

                          2. Right click the combined field you created and select "Edit Combine Field".  Clear out the comma in the "Separate members by:" box.

                           

                           

                          Regards,

                          Joshua

                           

                          Order.png

                          1 of 1 people found this helpful
                          • 10. Re: Is it possible to keep data from being sorted?
                            Josh Fields

                            Hey Guys,

                             

                            Thank you so much for the help. I got the charts looking the way they need to be.