4 Replies Latest reply on Dec 5, 2013 8:50 AM by Jim Wahl

    Sort is not including one value

    jonathan.richman.2
    jonathan.richman.2 Dec 4, 2013 6:42 PM

      This one is really confusing me. Sorting with Tableau is not a treat.

       

      Here's the problem. I've got this set up (see attached) to show the top customers (Customer Ref Number) by location (Store Ref). When I sort on the total sales, it sorts perfectly with the exception of one outlier for almost every location. You can see in the screenshot that a low value appears before the highest value and then the rest of the values for that location sort perfectly. This affects nearly every location. A few are sorted correctly.

       

      Any ideas what's happening here. I have no filters applied here at all.

       

      Thanks,

      JMR

      Screen Shot 2013-12-04 at 9.38.59 PM.png

      • 63 Views
      • Categories: Actions & Filters
      • Tags:
        • 1. Re: Sort is not including one value
          Son Stegmaier
          Son Stegmaier Dec 4, 2013 7:03 PM (in response to jonathan.richman.2)

          In your example, if you remove storeRef dimension, does Customer Ref Number 2615940 totals higher than Customer Ref Number 1995072?

           

          I bet that Ref Number 2615940 has a higher overall total.  Therefore, it shows above 1995072 after you add storeRef dimension.

          1 of 1 people found this helpful
          • 2. Re: Sort is not including one value
            jonathan.richman.2
            jonathan.richman.2 Dec 5, 2013 4:53 AM (in response to Son Stegmaier)

            This is correct. If I remove storeref, 2615940 has a higher purchase total across all stores.

             

            I ended up doing what's outlined in this comment in another thread: Re: Sort buttons in chart not working correctly

             

            Wondering if there's a simpler or different way to do this though.

            • 3. Re: Sort is not including one value
              Jim Wahl
              Jim Wahl Dec 5, 2013 8:36 AM (in response to jonathan.richman.2)

              You could add another discrete column to the left of the Cust Ref Number to drive the sort. Easiest thing would be to use SUM(Total).

               

              Ctrl-click on the green SUM(total) pill in the Columns shelf and drag it to the Rows Shelf. Click the pill and select Discrete. Then move it to the left of the blue, discrete Cust Ref Number. Click on the SUM(total) pill and then click the Sort Icon on the toolbar. Finally hide the column by clicking the pill and deselecting Show Header.

               

              Jim

              • 4. Re: Sort is not including one value
                Jim Wahl
                Jim Wahl Dec 5, 2013 8:50 AM (in response to Jim Wahl)

                Sorry just tried this and it seems a bit finicky. For example, if you click the X axis and hit the sort icon, it  sorts everything ascending and you're stuck until you click the discrete pill again and click the descending icon.

                 

                It might be safer to use convert the SUM(total) pill to a rank table calculation (assuming you have Tab 8.1; if you don't you could use index(), but it's a couple of extra steps). This appears to override any user-selected sort from the axis. I did this by right-clicking SUM(total) > Quick Table Calculation > Rank. Click again > Compute Using > Custom Ref Num.

                 

                This will also make it easier to apply a filter like Top 5 by filtering on the rank column.

                 

                Jim