14 Replies Latest reply on Dec 9, 2016 4:24 AM by Simon Runc

    Rank - Calculate when field not in table

    Andrew Hills

      Hey,

       

      I'm Sure this has something to do with LOD and rank fields, but I can't get it to work with my limited knowledge, and I can't find the right phrase for google-fu.......GAH!!!

       

      Anyways - I have a Rank function on a sales figure that gives a position per week for each ISBN for each year. Easy: Address Company Name and ISBN, Partition on Week & Year.....great. Works fine. I then want to group those into bands. No probs - calculated field showing: IF [Rank Function] < x then "TOP x" else.....again works great.

       

      However, I want to count the number of ISBNs in each banding. Now normally I'd just pull the ISBN and week info from the rows or columns shelf, and it'd add them all together. However doing so pulls it out of the table calc. I can't put it in detail because that still splits it out.

       

      So I need the table Calc to use Week and ISBN, but I need to have them somewhere that doesn't affect the table (The Filter Shelf doesn't work either)....

       

      Any Ideas?

        • 1. Re: Rank - Calculate when field not in table
          Norbert Maijoor

          Andrew..

           

          Something like {fixed week: count(ISBN)} or {fixed year: count(ISBN)} .........

          • 2. Re: Rank - Calculate when field not in table
            Andrew Hills

            Hi Norbert,

             

            Thanks for replying. I managed to get something like that by using excluding week and ISBN from the SUM(No. of records) measure. However while it returns the correct value, it just replaces the multiple versions of the previous count in each cell (which all had the value of 1) with the total. It doesn't stop the "cell" being split by the detail fields (Week/ISBN) - removing them means the Rank function doesn't work.

            • 3. Re: Rank - Calculate when field not in table
              Norbert Maijoor

              Hi Andrew,

               

              Can you share your workbook?

              • 4. Re: Rank - Calculate when field not in table
                Andrew Hills

                Not really - sensitive info n such.

                 

                Essentially, the problem is:

                 

                • If I compiled the Top 1000 Selling Computer Games every week for 3 years, how would I make a table that showed how often a particular Game Developer appeared in the Top 10, 10-20, and 20-100 for each year?

                 

                The table must have the Game Company on the Row Shelf, and the Year then the Rank Banding (Top 10, 10-20, 20-100)  on the column shelf.

                 

                The sample data would have fields for

                1. Game Title
                2. Game Developer
                3. Year of Sale
                4. Week of Sale (1-52)
                5. Amount Sold

                 

                 

                That's it. At the moment I can do the Ranking part easy, but I have to include additional fields in the table to get the Ranking to work, which screws up the display

                • 5. Re: Rank - Calculate when field not in table
                  Norbert Maijoor

                  Hi Andrew

                   

                  Can't get "my head around" the count within the Top 10. Able to get count for selected year based context filter but not able to count within Top per week/current year

                  Would like to ask Simon Runc to step in. Simon Runc would you be able to get Andrew back on track. Appreciated!:)

                   

                  • 6. Re: Rank - Calculate when field not in table
                    Andrew Hills

                    Hi Norbert,

                     

                    Right I've managed to randomly generate a solid Top 50 for 10 different developers for 50 different ISBNs, and load it into a workbook (Attached).

                     

                    So as you can see I've got it sorted to the stage where I can identify, group, and get them all corralled correctly into the table. However, I'm struggling to add that cell together to produce a single value. All I need is to combine all those individual 1's in the worksheet "Output Table" into a single figure.

                     

                    I thought I'd managed it using a Total Function (See worksheet "Output with LOD Calc"), but then discovered I couldn't get it to Partition on the Rank Groupings field, which I think would've fixed it...

                     

                    I mean - it looks like it should be simple, right???

                    • 8. Re: Rank - Calculate when field not in table
                      Simon Runc

                      thanks for the Norbert Maijoor ...apologies for the delayed response...I actually took 2 whole days off (and under wife's instructions...Tableau free!!). Yes this one is a toughie!!

                       

                      hi Andrew,

                       

                      Yes this is a tricky one...there are 2 ideas that would make this much easier which you might want to vote up

                      Solution 1, we can't partition on Table Calcs (meaning we can't use the Bins to partition the data)

                      https://community.tableau.com/ideas/1194

                      Solution 2, we can't use RANK function in LoDs

                      https://community.tableau.com/ideas/4553

                       

                      While waiting for either of the above to be implemented...we have to get a bit creative!

                       

                      So we do need to keep both ISB and Week/Year in the vizLoD, as the RANK needs this to run the Rank correctly, meaning we have a different calcLoD and vizLoD.

                       

                      so first thing is to create our Rank formula

                      [Sales Rank - SR]

                      RANK(SUM([Sales]))

                       

                      and we set this to ruin like the below image, this is shown in 'Working - SR 1' tab

                       

                      The nest thing we need to do is group these ranks into Top 10, 20 or 30...Now as we can't partition on Table Calcs we need to create a calculation per bin. below is the one for Top 10 (the other 2 are similar)

                      [Bin Counts - Top 10 - SR]

                      WINDOW_SUM(

                      IF [Sales Rank - SR]<=10 THEN 1

                      ELSE 0

                      END)

                       

                      now as we have a nested Table Calc, we can set these up to run slightly differently

                      So [Sales Rank - SR] is set up as before

                      but the [Bin Counts - Top 10 - SR] is set up to SUM up the number in the Top 10 for each Game Developer and needs to SUM up the weeks. This is shown below and in the 'Working - SR 2' tab (btw this also triggers data densification...but this isn't a problem here)

                       

                      So we now have the right value, but it is plotted multiple times (one mark for each of the VizLoD...ISB/Week), so we add a further nested Table Calc to only return the 1st mark ('Working - SR 3' tab shows how this is working)

                      [Bin Counts - Top 10 - SR FIRST]

                      IF FIRST()=0 THEN

                      [Bin Counts - Top 10 - SR]

                      END

                      and this 3rd nest is set up as (with the other 2 as before)

                       

                      So after setting up the other Bins (top 20, 30) in the same way we end up with

                       

                       

                      Personally, I'd opt for trying to get the week/year/ISB rank coded into the data...as this will make life so much easier, but if that's not an option it is possible (just very complicated!!)

                       

                      Hope that helps, and makes sense, but let me know if not.

                      1 of 1 people found this helpful
                      • 9. Re: Rank - Calculate when field not in table
                        Norbert Maijoor

                        Morning Simon,

                         

                        Glad you are "Allowed" again;)

                        Tableau should start some kind of a "Lady's program" explaining why the "Nerds" are enjoying themselves so much:) and what is beneficial about it for the lady's....

                        • 10. Re: Rank - Calculate when field not in table
                          Yuriy Fal

                          Simon, I like it very much

                          that you've got Top-N literally

                          and counted # in overlapping bins :-)

                           

                          If the TC question is about counting non-overlaps --

                          mean Top  1-10, 11-20, 21-30 and 30+ --

                          then a good old "Count by RANK" method would work, too.

                          Different calcs, same Addressing & Partitioning :-)

                           

                          Please find the attached.

                           

                          Yours,

                          Yuri

                          2 of 2 people found this helpful
                          • 11. Re: Rank - Calculate when field not in table
                            Simon Runc

                            hi Yuri,

                             

                            Awesome solution...when looking about for a solution I did find that method in Alexander and Jonathan's blogs

                             

                            Vizible Difference: Histogram via Rank Functions

                             

                            Counting from Nothing – A Double Remix (or, Partitioning via Table Calculations v2) | Drawing with Numbers

                             

                            but couldn't quite get it to work (definitely a limitation on myself, and not their posts). Having seen your solution (and understanding this exact example) the "penny has dropped"!!, so many thanks for posting.

                             

                            I wasn't sure if the OP wanted to have "overlapping" bins or not...in my version I can add an "AND" to make them exclusive

                             

                            [Bin Counts - Top 20 - SR]

                            WINDOW_SUM(

                            IF [Sales Rank - SR]<=20 AND [Sales Rank - SR] >10 THEN 1

                            ELSE 0

                            END)

                             

                            ...but for non-overlapping bins, the "Count by RANK" is much more elegant.

                             

                            Thanks again...I've learned a lot doing this one!!

                             

                            Andrew, Something I forgot to mention...In my solution, I have restricted the weeks from 1 to 3, just so it was easier to check the results.

                            • 12. Re: Rank - Calculate when field not in table
                              Yuriy Fal

                              Thanks Simon,

                               

                              Frankly, I'm just standing on the shoulders of giants :-)

                               

                              I have a compendium of the most common techniques -- 

                              and keep suggesting them over and over again on Forums.

                               

                              In-calc filters, conditional aggregate ones, custom totals, table calc bins,

                              sheet swapping (including the nifty ... with Action Filter one :-) -- you name it.

                               

                              Yours,

                              Yuri

                              1 of 1 people found this helpful
                              • 13. Re: Rank - Calculate when field not in table
                                Andrew Hills

                                Simon Runc, Yuri Fal, Norbert Maijoor

                                 

                                Oh Wow. Brilliant! I was getting them to total together and then just scratching my head - of course showing only the first in the list is the next step! I need to think more in nested calcs...

                                 

                                I'm not entirely sure if I will be needing overlapping or non-overlapping bins...so having both methods is gold.

                                 

                                Thanks a bazillion everyone!!!

                                • 14. Re: Rank - Calculate when field not in table
                                  Simon Runc

                                  I have a compendium of the most common techniques --

                                  and keep suggesting them over and over again on Forums.

                                  ...nice to hear, glad I'm not the only one