6 Replies Latest reply on Feb 26, 2016 10:30 AM by Mikey Michaels

    Rank calculation issue

    Mikey Michaels

      Hello Friends,

      Quick question here- On the attached, I'm trying to display the top N customers based off my parameter "Top Customers 1". Everything seems to work correctly until I begin to filter my view. For instance, if I select "STS" instead of "All" on the Business Segment quick filter, Tableau doesn't display the specified number of customers based off my Top Customers 1 parameter.

       

      Any idea how to resolve?

       

      Regards,

      Mikey

        • 1. Re: Rank calculation issue
          Joe Oppelt

          You're getting caught by table calcs.


          When you use table calcs for filters, it doesn't filter out rows that get evaluated by the sheet.  It just filters out what actually gets displayed on the sheet.

           

          What that means is that your RANK function is still looking at [Selected Over Text] rows that you have not selected in the filter.


          I modified your sheet a bit.  I created two more rank calcs -- just spitting out the raw rank numbers.  rank_top (copy) gets evaluated using Table(down) as you were previously doing.  I put that on the text shelf with your data.  You can see that you have a whole bunch of values less-than-or-equal-to 5.  The second one  -- rank-top (copy) (copy) is the same internal syntax, but I have it running along all the dimensions on the sheet.  (Restart = none).  You will see that this iteration of the rank value is almost always larger than the Table(down) implementation.

           

          Look at sheet 3.  It's essentially a copy of Sheet 1, except I put [Seats UM diff] on the rows shelf, made it discrete, and moved it to the front of the line.  (This is a tableau trick to sort a table calc.  For the record, this trick always sorts ascending.)  Go to the bottom of the sheet.  that's where the largest values are.  You can see that the second RANK is in order from the bottom up.  This demonstrates that this is the way you actually want to implement your actual [rank_top] calc in the filter.  You'll always get the top 5 of whatever is selected via business segment.

           

          This will probably raise more questions for you, which I'll be glad to answer.

          • 2. Re: Rank calculation issue
            Mikey Michaels

            Joe, thank you so much...you have help me out tremendously in the past!

             

            As you expected, I have a couple questions:

             

            I followed your instructions above and I believe I have the correct RANK used in my viz and filter. However, when I filter on Business Segment = Manufacturing, I only get 4 records instead of the specified 5. But, if I change the Business Segment to Lifecycle Collaboration, then I get 5...weird.

             

            Also, I do not want Business Segment in my viz. Once I remove, the RANK calculation becomes red, I then change the partitioning/address fields, however I still don't think that solved the problem. I bring this up as I may want to see the top 5 by Customer type and not Segment. Basically, I want Tableau to show me the Top N customers and have the ability to add multiple quick filters, but still retain the top N. Hopefully I'm not being too confusing.

             

            Thanks for your time!

            Regards,

            Mikey

            • 3. Re: Rank calculation issue
              Joe Oppelt

              The thing that's messing you up here are the table calcs on the filters shelf.  When you use a table calc as a filter, rows stay in the table, but just get filtered out of what is displayed.  To demonstrate, I created a calc to show the highest RANK value in the table.  It's displayed in the title.  The highest value will always be 1.  But in the attached I have changed the filter from SHOW to HIDE so that HIDE displays.  Scroll to the bottom.  You will see that the best RANK value on the display is 6, but tableau still knows about 1-5 behind the scenes.

               

              We can eliminate some of the table calcs.  Looking at the [Select Over Text] field, you are using LOOKUP to build the [Year Ago] and [Previous Text] strings.  From that point forward, anything that uses [Year Ago] or [Previous] inherit the characteristics of a table calc.  You can change the way those underlying calcs work to do string manipulations instead.  They might end up getting wordier than a quick little lookup, but you'll have more flexibility in what you are aiming for if you don't have table calcs on the filter shelf.

               

              Likewise, [Rank_top] is a table calc by virtue of the RANK function.  When you do the Show/Hide treatment, your filter is still keeping all the rows in the table but just hiding what you see on the sheet.  You can see that in the [Highest RANK] calc I added here.  You can keep your Top-N-Customers parameter and let the user specify that.  You can also add a parameter that lets the user pick whether he wants include Top-N or exclude, or see Bottom-N or whatever other flexibilities you want to weave onto the sheet.  If you want to see bottom-N, you can create a calc like my [Highest Rank], but just do window_max instead.  Don't forget to run it ADVANCED along all the dimensions on the sheet.  Once you have the machinery of parameters you want the user to have so you know what he wants to see, do a table calc that has a bunch of IF logic to accommodate all the parameter choices.  If I guided you correctly, this will be the only table calc that will be a filter.  You will have RANK values from 1-N.  Pick out the values the user wants to see, and set the output of this calc to 1 or 0 and select for 1 on your filter.

               

              IF [top or bottom parameter] = "Top" //  top-N logic

              THEN

                IF [hide or show parameter] = "Show" //  show top-N

                THEN

                    IF  [Rank value] >=  [how many parameter] THEN 1 ELSE 0 END

                ELSE        //  hide top-N

                    IF  [Rank value] <  [how many parameter] THEN 1 ELSE 0 END

                END

              ELSE      //  bottom-N logic

                IF [hide or show parameter] = "Show" //  show bottom-N

              etc. 

               

              ...  (you'll be messing with WINDOW_MAX([Rank value] in this part, showing/hiding from window-max through window-max minus [how many parameter]

               

              I typed the above off the top of my head.  You may need to correct some syntax.

               

              So all the RANKs will always be on the table, but the calc above will determine which ones to show on the sheet.  But when you eliminate all the other table calcs in the filters, actual rows will be thrown out of the table.  If you do that, you'll get the behavior you're looking for.

               

               

              And you can still have [business segment] on the sheet but not displayed.  If you leave it on the rows shelf, just right-click and uncheck "show header".  Or you can just drag it down to the data shelf.  And having said that, you may not need it at all.  I initially threw it in there to see what was actually going on in the displayed rows as I added/deleted segments.  If you take it off the sheet entirely, yes, initially the table calcs that reference [business segment] will turn red.  That's because in the ADVANCED section it's listed in there now.  Edit ADVANCED again and take it out.  (Actually, once you go into ADVANCED, it will already be gone.  When you save, the pill will go back to green or blue automatically.)

               

              Right now we also have Seats UM Diff on the rows shelf.  That's just there for demonstration purposes to sort the sheet by that table-calc value.  You can pull it off.  But maybe you want that sort in there!  (And if you want it sorted descending, make a duplicate calc, and just multiply the whole thing by -1.  Tableau will still sort ascending, but negative numbers sort ascending in appearance.  And if you don't want the value displayed (usually you don't when you use this technique), just unclick "show header" on it.  I also have show/hide on there for demonstration purposes.  Yank that off too.

              • 4. Re: Rank calculation issue
                Mikey Michaels

                Thanks again Joe for such a thorough response- I really appreciate you walking me through everything!

                 

                I will admit, I'm a Tableau novice and still a bit confused- maybe I'm just tired.

                 

                I have a follow up question. I was able to get the bottom N by creating two table calculations- [rank_bottom] and [sort]. This seems to get me the bottom N even though I'm filtering by a table calculation - [selected over text]. However, when I change the formulas to get the top N, I don't get what I'm after. Any idea as to why this is occurring?

                Why can't I just adjust the two calculations above to get the Top N?

                 

                As always, thanks for your help!

                 

                Cheers,

                Mikey!

                • 5. Re: Rank calculation issue
                  Joe Oppelt

                  I'm not sure you're getting what you want.  (Or else I'm not understanding what you want...)

                   

                   

                  If you turn on all business segments, you'll still get 5 rows.  I'm guessing that's what you want.


                  But if you also turn on two [Select Over] ranges, you'll get 10 rows.  (And 15, and 20, as you add more.)  Is that what you want?

                   

                  If you only want 5 rows across two (or more) period ranges, this is not set up properly yet.

                   

                  After I understand this point, then we can address the TOP vs BOTTOM question.  (And when you say you change the formula, is it just a matter of taking out "asc" from [Rank_bottom]?)

                  • 6. Re: Rank calculation issue
                    Mikey Michaels

                    Yes, you're correct, Joe. My last comment was incorrect. I want the Top N even when 2 periods are selected on my table calc [Selected over]. I thought I had figured out the bottom rank, but obviously that is incorrect.

                     

                    I need to go back and thoroughly look at your explanations- I appreciate your time and patience...I'm learning a ton!