11 Replies Latest reply on Jan 22, 2014 7:29 AM by Joshua Milligan

    Trying to use 8.1 Rank function

    Paul Senatori

      I'm trying to use the new Rank function to generate the rank value for a given brand at different time periods, i.e. Current Month, Prior Month and Prior Year. The ranking is done against a proprietary brand value in our extract.  I can generate rankings for these time periods, but only if I show every brand in the Category within which the brand belongs.  I want to exclude all the other brands in the final display of the results and only show Brand X' ranking at these three time periods.  If I "exclude" or filter out the other brands, the remaining X brand always has a rank of 1.

       

      Help?

       

      Thanks

      Paul

        • 1. Re: Trying to use 8.1 Rank function
          Joshua Milligan

          Paul,

           

          This is a case where you will likely want to do some kind of late filtering.  Instead of just using [Brand] as a filter, try creating a calculated field with the code:

           

          LOOKUP(ATTR([Brand]), 0)

           

          and then placing that field on filters.  You can select only the single brand you want and the ranks should be kept intact.  This is due to how Tableau operates behind the scenes.  With a normal filter based on a dimension, Tableau passes the filter to the data source engine, brings back aggregate results, and then applies table calculations (such as Rank).  By using a table calculation such as LOOKUP as a filter, you allow Tableau to bring back all the dimensions (e.g. Brand), do the Rank, and then do the filter (effectively hiding values from the view rather than filtering at the source).

           

          Regards,

          Joshua

          • 2. Re: Trying to use 8.1 Rank function
            Paul Senatori

            Joshua - Thanks.  That did the trick.  A lot of the functionality in our brand and trend dashboards leverage rankings and this enables us to drill into a specific trend while still providing a way to display its broader position in a category, i.e. its table rank position at different time periods.

             

            Paul

            • 3. Re: Trying to use 8.1 Rank function
              Joshua Milligan

              Paul,

               

              You're welcome!  Glad to help!

               

              Regards,

              Joshua

              • 4. Re: Trying to use 8.1 Rank function
                Paul Senatori

                Joshua.  The only thing I can't figure out is how to use it as a filter across multiple worksheets.  I can use it within a single worksheet, i.e. Quickfilter on Brand and it shows me the rank across the three periods for that single brand, but when I drop it into a dashboard the "Apply to Worksheets" option is not available?  I've tried lots of different options to see if will apply, but nothing seems to be working.  Is it a limit in the ATTR function? I can get other dimensions to work from that same worksheet as global filters, but just not my new Brand filter.

                 

                Thanks

                Paul

                • 5. Re: Trying to use 8.1 Rank function
                  Joshua Milligan

                  Paul,

                   

                  Since table calculations (any field in the view with the triangle/delta icon) are calculated based on the specific view, they cannot be used across worksheets.  If you only want to focus on one brand at a time, you might consider creating a parameter based on Brand and then creating a calculated field:

                   

                  LOOKUP(ATTR([Brand], 0) == Brand_Parameter

                   

                  and placing that calculated field on the Filters shelf of every worksheet in the dashboard (select True as the value to keep).  Then the parameter selection will update the filter of every worksheet. 

                   

                  Note two limitations of this approach:

                  1. Parameters are not dynamically updated based on the data.  So if new brands are added, you'll have to manually update the parameter.
                  2. Parameters are single select.  This will work for selecting a single brand.  You will have some additional flexibility not possible through filters.  For example, you could choose to highlight the selected brand in some worksheets  instead of filtering.

                   

                  Hope that helps.

                   

                  Regards,

                  Joshua

                  • 6. Re: Trying to use 8.1 Rank function
                    John Sobczak

                    Joshua,

                     

                    This is all great information!  I couldn't help think there might be other benefits of this approach as well. On your example of using a lookup table calc as a quick filter, does this also buy performance due to the query being sent to the database is not being qualified?  And if so, might this be a use case of using lookup table calcs as filters in lieu of standard quick filters?

                    • 7. Re: Trying to use 8.1 Rank function
                      Joshua Milligan

                      John,

                       

                      That's a great question!  I would defintiely welcome the insights of others on this question.

                       

                      My thoughts are:

                       

                      It could be more performant or less depending on the case.  Mostly, I think, it would have to do with the cardinality of the dimension and if your view was defined at that exact level of detail.

                       

                      So, take for example a view where you have the dimension Category and it has 3 values.  If your view is at the level of Category, then Tableau runs a query that groups by Category and pulls back 3 rows of aggregate data.  It might be more efficient to pull back those 3 rows and then hide one or two of them using a table calculation.

                       

                      But if you had 3000 or 300,000 values for Category, it would almost certainly be more efficient to filter out 2998 or 299,998 records at the source instead of pulling them across the network, loading them in memory, and then asking Tableau to do 3000 or 300,000 calculations to determine whether to render the data or not.

                       

                      I would recommend using table calculation filters intentionally to solve issues where you need late filtering of the data (e.g. to compare at different levels of detail or show parts compared to the whole).  Even in many of those cases, you'll want to use a regular filter to limit the set of data that gets pulled into the cache.  For example, if you want to show oinly the current month and also how it relates to the entire year you would, for performance, most likely still want to filter out the 10 years of history prior to the year in question.

                       

                      Regards,

                      Joshua

                      • 8. Re: Trying to use 8.1 Rank function
                        Paul Senatori

                        Just to finish off the thread, I tried a number of the above solutions and got most of them to work.  But in the end, the hurdle was controlling the parameter-based filter via another quick filter.  For example, within a dashboard the Brands are part of another dimension called Categories.  Example: Dell is a brand in the Technology Hardware Category.  The parameter approach worked great if I treated all brands as a single list.  But users like to "pre-filter" the brand list based upon a category selection.

                         

                        I couldn't get get the category filter to trigger the the parameter filter, so I went with a pre-query approach and aggregated all the rankings into a separate table that was then linked to the same datasource in Tableau.  Not as flexible, but my data is loaded monthly.  My new table has category, brand, current_rank, prior_month_rank and prior_year_rank.  Each month this table is updated with new rankings.

                         

                        Thanks for all the support on this one.  I still have that original lookup(attr... approach in my dashboards though!

                         

                        Paul

                        • 9. Re: Trying to use 8.1 Rank function
                          Joshua Milligan

                          Paul,

                           

                          That definitely sounds like a good approach given the additional limitation of the parameter you pointed out (it cannot show relevant values based on another filter).  There are a couple of ideas out there (both by Jonathan Drummey) to allow table calculations to be more global in application.  That would have gotten you over the first hurdle in the issue without resorting to a parameter and eventually hitting that limitation.  You might consider adding your vote!

                           

                          Regards,

                          Joshua

                           

                          http://community.tableau.com/ideas/2190

                          http://community.tableau.com/ideas/2413

                          • 10. Re: Trying to use 8.1 Rank function
                            John Sobczak

                            Joshua,

                             

                            Those are great thoughts!  After I thought about it more, it hit me that the key to this may your descriptor you mentioned of  "late filtering". So ultimately the same effort of filtering has to be done, whether at the data source or Tableau destination, and perhaps indeed the biggest determinant consideration is the volume of data being pulled over a network, loaded into memory, etc.

                            • 11. Re: Trying to use 8.1 Rank function
                              Joshua Milligan

                              John,

                               

                              I think that's exactly right.  The work has to be done somewhere and usually the source data engines are optimized to do those kinds of operations -- especially in one query.  Tableau is very efficient too, but pulling and loading a lot of data into local memory and then performing a lot of table calculations can be very costly.

                               

                              Regards,

                              Joshua