9 Replies Latest reply on Feb 5, 2019 7:51 AM by Sara Kim

    Top 4 Category with Fixed Ranking

    Sara Kim

      Hi Tableau Gurus,


      I need help with the following situation: I need to find Top 4 sub category for each customer based on value of Sales.

      I am able to find top 4  with index/rank. But I need Top 3 category which excludes Sub category = "Tables" and need "Tables" to always show as 4th RANK as fixed Rank, if it belongs for that customer.


      How can I achieve this fixed Tables subcategory as 4th rank always? I am attaching the sample superstore workbook. Thanks!


        • 1. Re: Top 4 Category with Fixed Ranking
          Joe Oppelt

          (10.5 here)


          See attached.


          I made a bogus calc that sets "Tables" to zero.  Whether or not it's a top-4, it just sets it to zero.  (I could have gotten fancier and did a window min of the top 4 and subtracted 1 or something.  Point is, I forced this calc to be the smallest of the top-4.)


          On Sheet 1(2) I filter to index <= 4.  And I made a RANK calc to rank the top 4 (as indicated by index), ranking on my bogus calc.  This moves Tables to the bottom (if it's even in there.)


          Finally, on Sheet 1(3) I moved that RANK calc so that it sorts the 4 rows.  You don't ever have to display the bogus top-4Sles calc.  I just did that to show my work here.

          1 of 1 people found this helpful
          • 2. Re: Top 4 Category with Fixed Ranking
            Sara Kim

            Hi Joe,


            Thank you so much for help! I believe "Table" sub-category is appearing within top 4 category for each customer and the Rank top 4 calculation works.


            But in the cases when the ranking for example "Furnishings" is at 5th, how can i make this appear at 4th Rank?


            Is there anyway to show Top 4 only if "Furnishings" belongs to a customer with Furnishings always "4th Rank" otherwise Top 3 Category for each customer?


            • 3. Re: Top 4 Category with Fixed Ranking
              Jim Dehner

              Good morning


              This is deceptively tricky


              see the attached


              I can't get everything you want - maybe Joe can figure it out

              First the Customer must have a record for Tables or tables will not show up

              Second I can force the record in the top 5 but I can get them to sort



              I started with an LOD for the sales

              then created 2 sets on subcategories



              then outer joined the sets


              and created a series of filters - see the viz on how the are set


              set the rank like this


              You are on 10.5 - in 2018.3 Action sets were introduced - they allow you to dynamically change the set members

              I played with action sets to on a single customer at a time and had some success sorting - but that only showed 1 customer name

              and honestly that was on an early attempt and I did not go back there


              Maybe Joe can figure out how to sort them the way you want - the problem I ran into is the combined set actions create another partition in the data and messes with the sort



              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              • 4. Re: Top 4 Category with Fixed Ranking
                Joe Oppelt

                I need to understand more.


                I shoved "Table" to position 4 only if it appeared in the original top-4.  But I can force it at the 4th no matter where it falls, if you want.


                Now you ask about "Furniture".  Is the target value for this exercise going to change?  How will it be selected?  (For instance, will the user select the value from a parameter?)  We can do that too.


                And yes, we can display only top-3 if there is no "Furniture" at all.


                So tell me what you're really aiming for, and we can make it happen.

                • 5. Re: Top 4 Category with Fixed Ranking
                  Sara Kim

                  Thanks Joe and Jim for your help with this. I really appreciate it!


                  Yes Joe - I need "Tables" to appear at 4th position no matter where it falls (even it falls at 10th ranking for example). The reason I was changing the exercise to "Furniture" from "Tables" because "Tables" was always appearing within top 4 and "Furniture" was appearing in other ranking for ie 7th rank. It does not matter, we can stick to "Tables" for this exercise- (whichever you prefer) but need to it to appear at 4th Position no matter where it falls.


                  and yes need to display Top 3 if there is no "Tables" otherwise need to display Top 4 (if there is Tables for that customer). Please let me know if this is still confusing.


                  Thanks alot!

                  • 6. Re: Top 4 Category with Fixed Ranking
                    Joe Oppelt

                    I'm getting back to this.  Hang on.

                    • 7. Re: Top 4 Category with Fixed Ranking
                      Joe Oppelt

                      OK.  Step by step.

                      On Sheet 1(3) I added another calc.  See [Does this customer have Tables].  This is a LOD that tells me if the customer has any Tables rows.  If the value is greater than zero, there are Tables for this customer.  This is a FIXED LOD.  That means it will operate no matter what filters you have on the sheet.  So even if you happen to filter out "Tables" for some reason, you would still know that the customer has Tables in his data.


                      Go to Sheet (4)..  First of all, I have filtered this down to just a few customers.  Some have no tables.  Some have Tables in the top-4 already.  Some have Tables below 4.  So we can test all the conditions.

                      And I tool off the INDEX calc.  I made my own RANK calc here.  In the long run it arrives at the same values at your INDEX did, but I control what is actually getting ranked, and it doesn't depend on any other sheet sort ordering.  And note the table calc setting I used.  Restart every Customer.  Every table calc I have added to these sheets uses that setting.

                      Next I created a calc to determine what position 4 holds.  See [What is in position4 sales].  I'll use this to force Tables to sort to the 4th position later on.


                      Next I made a new version where I force a new sort value, similar to my old [Top-4 Sales].  See [Top-4 Sales (copy)].  This is the key step in the way I approached this.  If the sub category is Tables, do things to force Tables to the 4th position.  If Tables is less than 4, then make the sorting values one less than the current 4th position.  If it's past the 4th position, bump it in front of whatever is in the 4th.


                      You can see the results of that on Sheet (4).  And finally I made a new rank of the calc that pushes Tables to the 4th position.  And you can see that where Tables exists, its number is always 4.


                      Go to Sheet (5).  I made one more calc to display top-4 (or top-3 if there are no tables.)  At this point you could remove [Rank Sales] from ROWS, and move [Rank Top-4 Sales] into that place, and the sub-categories would sort by that ranking value.  You don't have to display any of the interim top-4 calcs.  JUst leave SUM(Sales) on the sheet, and all the other calcs will just work behind the scenes.

                      • 8. Re: Top 4 Category with Fixed Ranking
                        Sara Kim

                        Thank  you Jim! I tried this and was working for me - I added the following rank calculation to get the sorting in the correct order - so that tables will always come last....  RANK(IF MIN([Sub Category]) = 'Tables' THEN -1 else SUM([Sales]) end)

                        • 9. Re: Top 4 Category with Fixed Ranking
                          Sara Kim

                          Thank you Joe for your help!! I am taking a look at this to understand a bit more.