9 Replies Latest reply on Aug 27, 2013 5:49 AM by David Nelson

    How do I prevent sorting the rows in a crosstable?

    David Nelson

      I have a three-column spreadsheet and need the data crosstabulated, which Tableau does quite nicely. However, I need the data sorted by the "Columns" dimension ONLY and not sorted at all by the "Rows" field. The third field is put in the "Marks" area as "Text". To demonstrate, I get this from Tableau:

       

      FACTORY AFACTORY BFACTORY C
      CAR1YN
      CAR2ON
      CAR3OFF
      CAR4YN
      CAR5OFF

       

      And this is what I need it to be:

       

      FACTORY AFACTORY BFACTORY C
      CAR1YN
      CAR4YY
      CAR2ON
      CAR3OFF
      CAR5OFF

       

      The actual spreadsheet is 35K+ rows and there's a lot of data to analyze. I need to visualize it this way so I can determine the "ascending order" of the factories - so in this instance Factory A has all the car models Factory B has, as well as CAR1. A solution to my crosstable problem would be appreciated, but I'm open to any possible alternatives to analyzing this data in the manner needed with Tableau.

        • 1. Re: How do I prevent sorting the rows in a crosstable?
          Pedro Machado

          Hi David,

           

          This should help:

           

          Sorting | Tableau Software

           

          Also, as Shawn suggests, please post a packaged workbook to make it easier for people to help you.

           

          Pedro

          • 2. Re: How do I prevent sorting the rows in a crosstable?
            David Nelson

            That video unfortunately did not help with what I need to do; the samples provided in the clip do not address the problem I'm having. To state the problem again - I need a crosstable sorted by column only and not sorted afterwards by row, which Tableau seems to do automatically. I have tried clearing sort on the rows, changing sort parameters on the columns and adding a duplicate hidden column field alongside the rows. Sorting by field aggregation isn't applicable here; I can't copy/paste the 971 rows in the manual sort area in the order I need and I won't be going through all those fields clicking "move up/move down".

             

            I will not be attaching proprietary information to any post, which is why I created the sample data above. I'm not sure why attaching a workbook for this sort problem is required, as it seems like what should be a rather straightforward operation. Even if I were to attach a workbook in this instance, the replying poster would still have to explain how they "fixed" the workbook so I can use that knowledge later on instead of posting workbooks to be "fixed".

            • 3. Re: How do I prevent sorting the rows in a crosstable?
              Pedro Machado

              David,

               

              I am sorry the video didn't help.

               

              Tableau will always start sorting dimensions alphabetically. If that is not what you want, you need to tell it to use a different sort order, which may need to be based on a calculated field.

               

              I still don't understand the logic you need to sort the rows. Is it by the number of factories associated to each car so the car with the most Ys shows at the top? What about ON/OFFs values?

               

              To answer your questions about why a workbook is useful ... sometimes the best way to reach a solution is by trying a few things. This is true especially when table calculations may be required. By having a workbook that has certain volume of realistic (but fake) data and some attempts at the view you are trying create, it saves community members time (we don't have to spend time trying to create dummy data we may not know how to create and sometimes we can just make a small tweak to a view that is already created to get it to work).

               

              This document explains other reasons why packaged workbooks help.

               

              http://community.tableau.com/docs/DOC-5065

               

              Thanks,

               

              Pedro

              • 4. Re: How do I prevent sorting the rows in a crosstable?
                Tracy Rodgers

                Hi David,

                 

                One way to do this is to create a calculated field similar to the following:

                 

                if [FACTORY A]='Y' then 1

                elseif [FACTORY B]='N' then 1 else 0 end

                 

                Place this on the level of detail shelf. Right click on the Car column and select Sort...>Descending, Field: Calculated Field above, sum.

                 

                Hope this helps!

                 

                -Tracy

                1 of 1 people found this helpful
                • 5. Re: Re: How do I prevent sorting the rows in a crosstable?
                  David Nelson

                  Shawn, Pedro - I'll just go with the flow here, the workbook is attached and also located here if this helps: http://public.tableausoftware.com/views/Crosstable2Example/Sheet1?:embed=y&:display_count=no

                   

                  Tracy, the problem with coding calculated fields in the real application is that there would be 615 "FACTORY #" columns to calculate like this.

                  • 6. Re: How do I prevent sorting the rows in a crosstable?
                    David Nelson

                    So in the attached example, I need the data sorted by "TYPEGROUP" only with no sorting applied to "TRANSACTION". For example, I need all the FROG.PAD.* columns grouped and sorted but all their corresponding transactions not sorted so all the FROG.PAD.* data is clumped together instead of staggered like the universal sorting produces. This way I can visualize any patterns and map out the entire system without massive manual data entry.

                    • 7. Re: Re: How do I prevent sorting the rows in a crosstable?
                      Sarah Ebreo

                      Hi David,

                       

                      Does sorting TRANSACTION by TYPEGROUP provide the desired sort behavior? I have attached a revised version of the Crosstable2Example workbook, that illustrates this method.

                      • 8. Re: How do I prevent sorting the rows in a crosstable?
                        David Nelson

                        This might be it! I'll try to get back to this post tomorrow after playing around with it.

                        • 9. Re: How do I prevent sorting the rows in a crosstable?
                          David Nelson

                          Well, this is definitely what I need but how can I copy-paste this data to an excel spreadsheet using Tableau Public? Ultimately what I need to do is (using this chunk below as an example) establish the additive patterns of the privilege levels. So I not only need the permission settings bunched together as seen below (thanks!), but I also need the text data of "W" & "X" sorted in alphabetical order from left to right, as in:

                           

                          O-ENG-1     W W W W W W W W W
                          O-ENG-2     W W W W W W W  X  X

                          ADMIN        W W  X  X  W W W  X  X

                           

                          This way I can quickly determine the patterns and get on with the rest of this project. I'm pretty sure it would be too labor intensive to do this via calculated fields in a crosstab with over 37,000 records and we've already sorted all the permission data by TYPEGROUP to get this far. If I can get this to excel I will be taking these "chunks" one  chunk at a time and sorting the privilege levels by what their permission settings are within that chunk.

                          GFScreenshot05 Aug. 27 08.25 AM dnelson.jpg