8 Replies Latest reply on Jun 15, 2016 9:20 AM by cignior Branched to a new discussion.

    Ranking Trouble

    cignior

      Hello. Please see my attached TWBX file.

       

      I am trying to rank by Parent Account per territory and show the Ship To Accounts that are "underneath" each Parent Account. So in Excel, it would display as such:

       

      See how the Parent Account is listed from Greatest to least with the ShipTo Accounts? The only part that is missing in this Excel example is a column that would list the Parent rank (1,2,3,4...) per territory. You'll notice in my example TWBX file that I have an option for the user to choose which column he/she would like to rank by.

      Capture.JPG

       

      Thanks

        • 1. Re: Ranking Trouble
          Joe Oppelt

          Note to self:  Version 9.0 workbook here.  

           

          See attached.  I got you half way there.

           

          What doesn't seem to work now is subtotaling.  Is that necessary in your case?

           

          If the attached is getting you close, I can describe what I did in there.

          • 2. Re: Ranking Trouble
            cignior

            Hi Joe - thanks for the reply. I see you've totaled the units by territory. Is there any way to total them by parent? This would at least allow me the opportunity to perform a rank calculation on a "Total for Parent" measure.

            • 3. Re: Ranking Trouble
              Joe Oppelt

              Depends on what you want to do with that.

               

              Do you want ALL "Childrens Hosp" totaled across all territories?  Or do you want it totaled within a territory so you can rank the parents within a territory?  And if you want parents within a territory, how do you want territories ranked (if at all)?  You've changed the requirement here.

               

              Do you know what I did in Sheet 3 to get what I did?  I would rather help you understand how that works so you could apply the principles to get all sorts of rankings as you need them without having to keep coming back to me for each new requirement.

              • 4. Re: Ranking Trouble
                cignior

                I'm looking to total Children's Hospital (and each parent account) per territory.

                 

                Or do you want it totaled within a territory so you can rank the parents within a territory?

                Yes. Exactly!

                 

                Do you know what I did in Sheet 3 to get what I did?  I would rather help you understand how that works so you could apply the principles to get all sorts of rankings as you need them without having to keep coming back to me for each new requirement.

                Yes please. I'm not exactly sure what was performed. I see that it is different from my Sheet 1, but otherwise, I'm not sure what is being displayed.

                 

                Thanks

                • 5. Re: Ranking Trouble
                  Joe Oppelt

                  Look at Sheet 2.  I created a table calc called Total for Territory.  It's doing the same sort of logic as your RANK calc, except it's doing a WINDOW_SUM of the two measures.

                   

                  Make a copy of Sheet 1, and follow along with these steps:

                   

                  I added Total for Territory to the measures shelf so you can see the value it is calculating.  Right click on [Total for Territory] in the measures shelf and select "Edit Table Calc".  You will see that "Compute Using" is set to Advanced.  Click the pull-down for that box and select Advanced yourself.  It brings up another edit box.  When you first encounter this box, most times all your dimensions are in the left-side box.  You can select some or all of these dimensions to tell Tableau how to navigate your table when adding up the table-calc operation you are editing.  In this case I selected all the dimensions because I want Tableau to take them all into account, and I have Territory Name at the top for a reason I'll get to in a moment.  Click OK.

                   

                  You'll go back to the first edit box.  You will see another  setting for "Restart every".  Pull down that box.  I wanted Territory Name at the top of the list in the previous edit box because I want to restart every territory here.  What these setting do is tell Tableau to add up all the [Current month] or [last month] values (as specified by the Rank parameter).  Cycle through all the dimensions, but when we get to a new Territory, go back to zero and start again.  This gets calculated for each row, so you see 110 on every Boston row, for example.

                   

                  Now to sort by this number.  Tableau is not very good at sorting by table calcs, but we have a technique to make it happen.  I did control-click and dragged [Total for Territory] from the measures shelf to the Rows shelf.  This preserves the pill in the measures shelf, but adds it also to the Rows.  And it preserves the table calc settings you did in Measures when it gets duplicated to Rows.  (You could have also dragged it from your Measures list where all your fields are listed, but then you would have had to rebuild the table calc settings.  Not a big deal, but control-click-drag saves you some time.)  When you first put that on the ROWS shelf, it really messes up your visualization, but don't panic.  Tableau puts it on the shelf as a green pill -- continuous, and that triggers tableau to make a bar graph of it.  Right click on that green pill and change it from Continuous to Discrete.  [Total for Territory] now shows twice on your viz.  The one right after [Ship to account] is from the blue pill in the Rows, and the rightmost column is from the green pill in the Measure Values shelf.

                   

                  Now drag the blue [Total for Territory] pill in the Rows shelf all the way to the front of the list on the shelf.  You'll see that it becomes the first dimension listed in the viz, and it is sorted in ascending order.  This is the technique I referred to for sorting a table calc.  Make it discrete, put it at the front of the list, and Tableau will sort on it.  One limitation:  Tableau will only sort ascending here.  So ...

                   

                  Go to Sheet 3.  You will notice that I have done all the same stuff here, except I replaced [Total for Territory] with a copy that I edited.  It's the same calculation as the original, except I multiplied the output by

                  negative 1.   (Right now that column is hidden.  Right click on the blue [Total for Territory (copy] pill and check "show header" to see the values.  That's the trick for getting Tableau to rank a table calc descending.  And that's what you see in Sheet 4.

                   

                  More in next reply.

                  • 6. Re: Ranking Trouble
                    Joe Oppelt

                    If you want to rank parents within each territory, you'll do the same sort of thing as [Total for Territory (copy].  Make a copy of [Total for Territory (copy)] and rename it [Total for Parent].  Identical code in the calc.  Just a second copy of the same thing.  Drag that to the Rows shelf.  Change it to Discrete.

                     

                    Now here's the tough part.  You'll need to figure out what advanced settings you want for this table calc.  If you do the same thing as [Totals for Territory], you'll get the same results, of course.  You know you want to get a total of 40 for Joes Pediatrics in Boston, and 30 for Davis Clinic, etc.  That means you need the summing to restart every [Parent Account] this time.  Therefore in the Advanced setting for the table calc, you want [Parent Account] at the top of the Addressing list.  And you don't want to add the totals across Territories, else you'll get numbers for Joes Pediatrics added up for ALL territories rather than just for Boston.  So don't include Territory Name in the Addressing box.  But you want to add up for all the Ship To rows within a Parent Account, so you want to add that to the Addressing box.  Therefore you should have Parent Account and Ship to Account (in that order) in the Addressing box.  And you want to select Parent Account for Restart Every.


                    One last step.  You currently have [Total for Territory (copy)] at the front of the ROWS pills.  If you still want to sort by that, then leave it there.  But [Total for Parent] is at the end of the row, and you want to use it to sort.  Drag it so that it lands between [Territory Name] and [Parent Account].  Now it sorts within Boston, and within Philadelphia, etc.  (You can unclick "Show Header" so that users don't see the numbers.)

                     

                    Give this a try and see if you can get it to work for yourself.  If something goes wrong, ping back.

                    1 of 1 people found this helpful
                    • 7. Re: Ranking Trouble
                      cignior

                      Thank you Joe. I will report back once I try!

                      • 8. Re: Ranking Trouble
                        cignior

                        Sorry Joe. I just realized that I never got back to you. But this worked perfectly! And I understood it. Thanks again