10 Replies Latest reply on Nov 24, 2010 8:52 AM by Julia.Ryan

    Sort on a quick table calculated field

    . JasonMack

      I have a worksheet set up to examine changes in same center revenue for a particular month, this year vs. last year.

       

      I used the "Quick table calculation" to pick "year over year growth" on my revenue measure.  I have the store information (name, number) on the rows shelf and then the year and month on the columns shelf.

       

      I want to sort the list in descening order so that I see the stores with the largest % year over year revenue change at the top.

       

      Is it impossible to sort when you have a table calculation?

        • 1. Re: Sort on a quick table calculated field
          James Baker

          I think the key point here is that you want to sort not "the measure", but "the dimension *by* the measure".

           

          I'm attaching a workbook which shows "Product 2" sorted by "YOY SUM(Sales)".  Just select both the dimension and measure pills and then use the sorting buttons in the toolbar.

          • 2. Re: Sort on a quick table calculated field
            . marjenmiller

            How do you sort across MULTIPLE fields?  What if your workbook had more fields before the Product 2 Sub Category...for instance, ZIP Code and Product Line? 

             

            As far as I can tell, this won't sort properly within Tableau

            • 3. Re: Sort on a quick table calculated field
              Joe Mako

              marjenmiller,

               

              As far as I know, Tableau can handle sorting on multiple fields. Instead of using the toolbar sort button, right-click on the field in the Rows or Columns shelf and select "Sort...". This will bring up the advanced sort dialog box for that field. Repeat the process for each field you want to sort on. The precedence of each sort is based on the order of fields on the shelf, leftmost are sorted first.

               

              If this is not what you are looking for, please provide more details.

              • 4. Re: Sort on a quick table calculated field
                . marjenmiller

                I have a simple, one dimensional table (imported CSV file) that has (1) ZIP Code, (2) Product Line, (3) Product Item and (4) Count Sold. All fields are on the worksheet.

                 

                In addition, I have 4 calculated fields on the worksheet:

                (1) Cumulative (running total) Count Sold

                (2) Rank (ordering from 1 to # of Product Items - this is a field of "1" with a running total

                (3) Percent of total Product Items (Count Sold / subtotal of Product Line within ZIP Code)

                (4) Cumulative (running total) of Percent of total Product Items (#3 above)

                 

                I want to sort by descending Count Sold within each Product Line and within each ZIP Code.

                 

                If I only have one ZIP Code and one Product Line, the sort works fine. If I have more than one ZIP or more than one Product Line, the sort does not work properly.

                 

                I have tried sorting it multiple ways (right click on Product Item, sort by field), use the sort buttons, etc. Nothing works.

                • 5. Re: Sort on a quick table calculated field
                  Erin Easter

                  Hi,

                   

                  Let me see if I can help you, though I may have a simplified understanding of what you want to do. It sounds like what you want is to sort the Product Line and Product Item fields by Count Sold in descending order. The way you do that is right-click the Product Line field (I'm guessing it may be on the Rows shelf) and select Sort. Then in the Sort dialog box select Descending by Field. Select Count Sold as the measure and the appropriate aggregation (probably SUM). Do the same thing for the Product Item field.

                   

                  Take a look at the attached workbook as an example. The Regions are all listed unsorted so it is just the data source order. Then within each region the Product categories are sorted in descending order. So overall across all regions the Office Supplies category has the highest number of items sold. That means it is always going to be at the top of the list. This is different than what you might expect because Office Supplies may not be the highest selling category in the East in particular but it is the highest selling overall amongst all regions.

                   

                  The same goes for the sort on Sub-Category. That way as you look across the rows in your view you can make comparisons easily. Like in this case I can see that Chairs & Mats is the number 2 furniture item across all regions but in the Central region Tables are actually outselling them. I wonder why the Central stores are selling so many tables?

                   

                  Does this make sense?

                   

                  Erin

                  • 6. Re: Sort on a quick table calculated field
                    . marjenmiller

                    Hi Erin,

                     

                    Thanks for the reply.

                     

                    Your example makes sense, but it isn't what I'm trying to accomplish.

                     

                    My goal is to sort first by ZIP Code (ascending), and then sort by Product Line (ascending) within that ZIP Code and then sort by descending Sales Count (measured value) within the Product Line and ZIP Code.  The other fields (Running Total of Sales Count, Rank and Pct of Subtotal) are calculated fields.

                     

                    I have attached a data sample for you to look at.  I believe I have the sort set up correctly, but Tableau just won't sort it in the right order (note the 7th ranked Product and others which should be sorted a lot lower).

                     

                    This should be a simple thing to do, especially since it is a common thing to do in Excel or by SQL statement.  I could always presort the data in the correct order before I load it into Tableau, but I shouldn't have to!!

                     

                    Thanks!

                    • 7. Re: Sort on a quick table calculated field
                      Jeff Mills

                      Try this.  Does this work?

                      • 8. Re: Sort on a quick table calculated field
                        Michael Cristiani

                        Jeff,

                         

                        Brilliant.  Can you explain why this works, so the concept can be replicated?

                         

                        MANY BLESSINGS!

                        Peace and All Good!

                        Michael W Cristiani

                        Market Intelligence Group, LLC

                        • 9. Re: Sort on a quick table calculated field
                          Jeff Mills

                          You bet,

                          Notice that we created a set to accomplish the sorting within product line and zip code.  Sets are transparent to the base fields.

                          Well we need to use a set so that the product is transparent to the table calc and it doesn’t impact the running total.  However, we do want the product line and zip to impact the running total, but that is part of the set so it is already transparent to the table calc.

                          How do we get Tableau to recognize our dimensions?  Calculated Fields!!!

                          Now Tableau does not think they are part of the set and will partition the table calc at those fields.

                           

                          Does this explanation help?

                          • 10. Re: Sort on a quick table calculated field

                            I have upgraded the workbook to 6.0.  If you have the latest version, please use this workbook.

                             

                            In addition, there is a knowledgebase article on this topic: http://www.tableausoftware.com/community/support/kb/nested_sorting