9 Replies Latest reply on Nov 29, 2018 2:46 AM by Fergal Dalton

    Sorting or ranking within a dimension

    Kevin Lee



      I have two questions about sorting or ranking.


      1) You'll see from the attached workbook that I have a 100% bar chart, with rows indicating different groups, and different colours for different products.


      I'd like to sort each Group so that the product with the highest % is furthest left, then the next product etc. Each group's highest % product is different, so I'm basically looking for a way to sort within a row.


      2) There are certain products where the % is low or insignificant in each group.  I'd like to be able to group products under a certain % threshold (say, 10%) in an "Other Products" category. However, the identity of these "Other Products" is different depending on the group. Is there any way I can create these "Other Products" categories that are independent of other groups / rows?


      Let me know if this isn't clear!


      Thanks alot.



        • 1. Re: Sorting or ranking within a dimension
          Alex Kerin

          The key to this I guess is sets - that's how you do a nested sort. The attached solves the first point - to a degree - I had to manually color all of the members of a set according to the product. You could then fake a legend. The second point - I can't get my head round because I can't use a function that creates an 'other' bin in combination with [Group] to make a set to solve the sort issue

          1 of 1 people found this helpful
          • 2. Re: Sorting or ranking within a dimension
            Alex Kerin

            So I can create a fake set of course by just concatenating - this actually works, and you can use another calculation to blank out the labels for the others. You still have to set colors manually.


            I've added the break point as a parameter so you can see the effect of changing what counts as 'other'. I've also reversed the order - I think it looks better with the grey trail of other. Finally, I added a border to each segment so that you can still see how other breaks down.

            1 of 1 people found this helpful
            • 3. Re: Sorting or ranking within a dimension
              Kevin Lee



              Excellent - thanks alot! Pretty much what I was looking for.


              Only thing left really is trying to figure out how to sort out the legend. The current titles in the legend aren't overly friendly ("Group 1, Product A") etc - when all you really want is just "Product A".


              It seems that you can theoretically edit the aliases for sets (I say theoretically, because when using Tableau 7 editing aliases causes the programme to crash - http://community.tableau.com/thread/116297 - apologies for taking the liberty of reposting your workbook).


              However, it seems that with your "Fake data" sample (ie with the parameters etc), you can't change the names in the legend. Can you think of any way of getting around this one?


              Thanks again!



              • 4. Re: Sorting or ranking within a dimension
                Alex Kerin

                The only way I can think of doing this is to create a sheet with just a legend:


                And then stick this on a dashboard with the proper one - you'll then have to hide missing products, change one product to 'Other', and recolor all of them to match - less than ideal if the data is changing. Maybe there's a completely different way of doing this that solves both, but I can't think of it.


                • 5. Re: Sorting or ranking within a dimension
                  Joe Mako

                  How about something a bit more dynamic, that uses custom table calcuations, as in the attached.


                  I also added a parameter so you can adjust the percent "Other" threshold.

                  • 6. Re: Sorting or ranking within a dimension
                    Alex Kerin

                    So much nicer Joe. How do set the sort to keep the large category of other at the far right side?.

                    • 7. Re: Sorting or ranking within a dimension
                      Joe Mako

                      The mark type is a Gantt bar, with the bar segments location calculated with a running sum of the percent of total, with a nested sorting performed in the advanced compute using dialog with both dimensions on the addressing side (to get the effect of using the set for the compute using for sorting), and the restart every set to Group. There are a few other routes to get this result as well. When looking at the workbook, be aware that some of the table calc pills are nested table calcs, with multiple compute using settings for their formulas.

                      • 8. Re: Sorting or ranking within a dimension
                        Juan Pablo Cancino

                        Hey Master Joe, let's revive this post for two short questions. I'm facing pretty much the same problem, but with two minor differences that I'm not able to resolve. The first one is that I want the raw data, meaning that once I apply a SUM(Number of Records) over a specific dimension I get the data in the same format as in Kevin's file. I don't have any idea why is not working in this way. Secondly, I'd like to group dimension members that have a Running Percent of Total of, for example, 80% or higher, instead of those with percent of total lower that a particular threshold. Why? because I trying to analyze the pareto, and not the less relevant (In some cases if I add up all those little fractions might sum a big percentage)



                        • 9. Re: Sorting or ranking within a dimension
                          Fergal Dalton

                          Hi. Was looking for a solution on this issue, but sets didn't fix it, so I tried combined fields which seems to work better (see this idea here). Posting the result here for the record as I couldn't see it anywhere else.


                          Here's a simple data set. Within F1="A", F2 should be ordered D,F,E. Within F1="B", F2 should be ordered E,F,D. Considering only F2 and ignoring F1, ordering should be F,D,E.

                          Screen Shot 2018-11-29 at 10.36.10.png

                          However trying Tableau's solution here, we find ordering F,D,E equal to the ordering if F1 is ignored. Which is not the desired result. (perhaps related to this)


                          Screen Shot 2018-11-29 at 10.38.45.png


                          Create a combined field "F1,F2" (make sure it's that order, "F1,F2" and not "F2,F1"), insert in the Rows between F1 & F2, and sort on that.


                          Screen Shot 2018-11-29 at 10.28.47.png


                          Now sort on the combined field for the desired result.


                          Screen Shot 2018-11-29 at 10.39.56.png


                          You can now hide the header for the combined field for a prettier result.

                          1 of 1 people found this helpful