8 Replies Latest reply on Oct 14, 2016 7:42 AM by Joe Oppelt

    Excluding fields from Rank calculation

    Andrew Hills

      So I've recently come to a revelation concerning table calcs (ranks in particular) and suddenly felt like I could do anything......

       

      ......so yeah that lasted a long time.....

       

      I've hit a wall concerning having data in the view I want to be ignored entirely by the rank function. I thought I'd cracked it, only to find a beautifully frustrating quirk concerning null values....

       

      So say I have 4 dimensions: Year, County, Street Name, House Number Group.

      I Also have 1 measure: House value

       

      I want to rank the Total House Value for each Street, per county, per year....That's simple enough: Partition Year, County, and Address Street Name. However, I want to then break the results down in House Number Group (like 1-30, 31-70, 70+). After research I got the info I needed, Created a new variable (House Value w/ Number) that excluded the House Number Group Variable from it's calculation, and dropped the excluded dimension in to break up the results while maintaining the overall rank. And it seemed to work like a charm.....

       

      However, when I got further down the ranking list, suddenly there was more than one Street Name for that rank in the Year, County spot.....On closer inspection I saw that one of the House Number Groups for that Street didn't exist. Instead of leaving it blank, it had promoted the street name from the rank below into the rank above.....So say if Portland street was was tenth in Hampshire in 2005 for total house value, when I add in the grouping...it remains 10th for 1-30 & 31-70...but it is now 9th for 70+ because Mark Street in 9th position doesn't have any house numbers above 70......

       

      I imagine it must be possible for tableau to break down a table calc after it has been performed, but I'm struggling to do so using Adressing, Partitioning, and Exclude....

       

      Any ideas??

        • 1. Re: Excluding fields from Rank calculation
          Joe Oppelt

          Too many ***** to juggle conceptually for me.

           

          I would need a sample packaged workbook to play with.  I have messed with RANK and nulls too.  You can get around it, but it's going to be specific to your example.  (It might be as simple as putting the RANK calc on the filter shelf and exclude NULL, but it might be very involved under the hood somewhere in the RANK calc itself or in whatever feeds the RANK calc.)

          • 2. Re: Excluding fields from Rank calculation
            Lisa Li

            Hey Andrew,

             

            You've heard of table calcs, now get ready for... LOD's.

             

            The great or maybe confusing thing about Tableau is there are always multiple ways to achieve an end goal. For your case, have you tried LOD (Level of Detail calcs)? You can't use table calcs in LOD's but you can rank values by creating calculated fields. For some use cases, I found it beneficial to use LOD's like {fixed [Street]: sum([Total House Value])} and then dividing that value by max possible House Value fixed to Country or Year. Afterwards, if you only want the rank, you can create a "fake" one by using index().

             

            I don't know if that is exactly what you're looking for but if you attach a workbook or markup data, I can show you what I'm talking about more clearly.

             

            Here is also some literature if you're not familiar: Overview: Level of Detail Expressions

             

            -Lisa

            CoEnterprise | Home

            • 3. Re: Excluding fields from Rank calculation
              Andrew Hills

              Hi Joe, Lisa - thanks for the reply

               

              I've attached a quick workbook using random numbers (pulled out the year because I didn't need to additional split....

               

              On the second sheet you should see the problem.....if you flick between the two sheets you should see they are identical for ranks 1-4....however at rank 5, Devon is now split in two....this is because there is no data for "Devon", "Poland St", "1-30".....but Devon "Devon", "Poland St" IS the 5th highest. however, instead of simply leaving the section blank, it brings forward the 6th place value ("Devon", "Park Ave") and puts the value for "1-30" there to fill the gap......

               

              I had though that by using Exclude I would completely eliminate the Dimension from the calc - but instead it seems to create a sort of fake partition, throws out null values, then performs the table calcs.....

               

              ....I hope that's kinda clear?

              • 4. Re: Excluding fields from Rank calculation
                Joe Oppelt

                Looking at this.  (Note to self:  V10.0 workbook.)

                 

                What are you actually ranking here?  Even on the first sheet, I can't tell what you want to consider #1 and #2, etc.

                • 5. Re: Excluding fields from Rank calculation
                  Andrew Hills

                  It's the rank of the streets (by the total value of all the houses) for each county (there are 6 counties - hence the 6 number 1s/2s etc etc)

                  • 6. Re: Excluding fields from Rank calculation
                    Joe Oppelt

                    I get it now.

                     

                    See attached.

                     

                    I added a calc to the text display to show what Tableau was doing in there.  It's the sum of the house number groups, across each row.  (See the "Rankings within..." sheet.)  I'm not really sure why we're seeing Park Ave in rank=5 there, but I took a different approach.  I ranked by the window_sum table calc instead of just the SUM(Sales...]).  See Sheet 4.  The [Ranking (copy2) value is after the street name.  It's interesting to see what it's doing with Park Ave there.


                    Sheet 5 I replaced [Ranking] with [Ranking (copy2)].  You get the proper ranks now.

                    3 of 3 people found this helpful
                    • 7. Re: Excluding fields from Rank calculation
                      Andrew Hills

                      WAHOO!!!!!!

                       

                      Brilliant thanks - that even makes a crazy kind of sense as well.

                       

                      Works like a charm!

                       

                      And so enduth 3 days of shouting at tableau - now I can sleep...........

                      • 8. Re: Excluding fields from Rank calculation
                        Joe Oppelt

                        Andrew Hills wrote:

                         

                        WAHOO!!!!!!

                         

                        ...now I can sleep...........

                        I am your resident Lunesta pill.