5 Replies Latest reply on Jun 28, 2012 7:36 AM by Maria Fernandez

    Rank as first row on cross tab

    Maria Fernandez

      I have read several threads about ranking using the index function.

      I understand that the logic behind Tableau data engine may not allow something like this.

       

      Rank%ProductComponentSold
      126.32%Product 1Component 120
      219.74%Product 2Component 215
      319.74%Product 3Component 315
      411.84%Product 4Component 49
      511.84%Product 5Component 59
      65.26%Product 6Component 64
      71.32%Product 7Component 71
      81.32%Product 8Component 81
      91.32%Product 9Component 91
      101.32%Product 10Component 101

       

      Any ideas for getting similar results.

       

      Thanks

       

      M.


        • 1. Re: Rank as first row on cross tab
          David Spezia

          Hey Maria,

           

          I would use the index function in this case. It fits the use case to a "T".

           

          Step 1 - Create a Function Called "Rank" with the formula "Index()"

           

          Step 2 - Drag the "Rank" Calculation to you Columns Shelf

           

          Step 3 - Change the Compute Using via the Pill Menu to the '%' column

           

          That should get you what you are looking for.

           

          -David

          • 2. Re: Rank as first row on cross tab
            Maria Fernandez

            Hi David,

             

            Thanks for your quick reply.

            I have followed steps 1-2, but % does not appear as option for computing on the pill menu.

            Could use the following sample to show me how?

             

            Thanks,

             

            M

            • 3. Re: Rank as first row on cross tab
              David Spezia

              Hey Maria,

               

              It was a bit of a convoluted process, but I got it to work (Attached).

               

              Issue:

              We not use a Table Calc as a Compute Using for a new Table Calc.

               

              Resolution (Case Specific):

              In order to get this to work I first had to add a Percent column to the raw dataset, this is because there we no measures in the dataset other than what Tableau calculated.  Second, I added an advanced Table calc for the Rank function that ranked by the Sum of Percent for Product and Component Descending.  Then in order to get the sort to function, I had to create a Set for Product and Component.

               

              -David

              • 4. Re: Rank as first row on cross tab
                Jonathan Drummey

                There's a way to do this that doesn't require adding a column:

                 

                1. Create a set on Product, Component.

                2. Drag the set as the first item on the Rows shelf.

                3. Click on the set's pill on the Rows shelf and set the Sort of the set to Descending, Sum of Number of Records.

                4. Click on the set's pill on the Rows shelf and uncheck "Show Header". This hides the set while we retain the order.

                 

                The table calculations can both be left at Table (Down) and will still work.

                Now, to get the columns in the order that were shown:

                 

                1. Drag the Rank measure onto the Rows shelf.

                2. Click on the green Rank pill and set it to Discrete. The pill now turns blue.

                3. Drag the blue Rank pill over to the left, just after the set pill.

                4. Repeat steps 1-3 for the % measure.

                5. Showing the last column can be a little messy. You can use the steps 1-3 above, or you can put SUM(Number of Records) on the Text Shelf (but not have a header), or leave Measure Names on the columns shelf, drag Number of Records onto the Text shelf, and then set the alias for No Measure Value to "Number of Records".

                 

                Sample workbook is attached, though it's a 7.0 workbook.

                 

                Jonathan

                • 5. Re: Rank as first row on cross tab
                  Maria Fernandez

                  Sorry for the 3 months delay replying, but just to let you know your solution worked perfectly.

                   

                  Thanks,

                   

                  Maria