2 Replies Latest reply on Mar 30, 2016 8:38 AM by Troy Brommenschenkel

    Rank String

    Troy Brommenschenkel

      Hello All,

       

      I am trying to create a worksheet for a dashboard that will show a rank and the "sub-category" tied to the rank in a text string.  For example, "[State] is ranked [Rank] for [Sub-Category]."  My problem is, that I can't seem to figure out how to call upon the rank that has been calculated on a separate worksheet.  I tried circumventing using a separate worksheet by using an LoD, but was then reminded I cannot use a Table Calc within them.  So far I think I am only able to do this by creating a string worksheet for every [Sub-Category] and filtering [State] with a string parameter.  Therefore, if the [State] = [Parameter], then it would pull the rank for the specific [Sub-Category].

      To complicate things further, I only want the text string to "show" if the rank is <= 20.

       

      I have attached an example of the rank worksheet in the superstore data set.

       

      I hope this was descriptive enough.

       

      Thanks!

       

      Troy

        • 1. Re: Rank String
          Simon Runc

          Hi Troy,

           

          So, unlike Excel, you can't create calculations in one sheet and reference them in another. Also when you are using Aggregate calculations (SUM/MIN/MAX...etc) or Table Calculations (WINDOW_SUM, RANK...etc.) the level of detail in your sheet (known as the VizLoD) will affect the result. This means in order to use the RANK function, and get it to RANK each State by Sales in a Sub-Category, you need to have both Sub-Category and State in the VizLoD. Let me know if that doesn't make sense and I can point you to a few resources (although the on-demand videos on calculations are very good at explaining the different calculation types in Tableau).

           

          Once we have this, building up a string is fairly straight forward as we can use + to concatenate parts together.

           

          So in my example here I have the following formula

          [Sales Rank Text]

          IF RANK(SUM([Sales]),'desc') > 20 THEN NULL ELSE

          ATTR([State]) + ' is ranked ' + str(RANK(SUM([Sales]),'desc')) + ' for ' + ATTR([Sub-Category])

          END

           

          I've had to wrap the State and Sub-Cat in am ATTR as the RANK is a table calculation (and so Aggregates) and I can't mix aggregates and row-level calculations. As we have both State and Sub-Category in the VizLoD ATTR([State]) is just the State.

           

          Hope that helps and makes sense.

          • 2. Re: Rank String
            Troy Brommenschenkel

            Thanks Simon!  This worked beautifully!  I did have an issue with Aliases not being referenced in the calc results, but I managed to find a workaround by separating the first portion: "IF RANK(SUM([Sales]),'desc') > 20 THEN NULL", out and just filtering the number of ranks I wanted to show.

             

            Thanks again!