4 Replies Latest reply on Sep 1, 2013 10:11 PM by Rajini S

    Sorting issue with LOOKUP function

    Rajini S

      Hi All,

       

      I am facing issue to sort the calculated measure. I am displaying values based on parameter selection for 3 measures. Shipping measure have calculation to have one week behind the data and used LOOKUP function to get one week behind data. Country trend have to sort based on selected measure by country based. My report will display max week by default and used RANK to achieve this.

       

      My problem here that, 'Select Measure' field is not appearing in Country sort.

       

      'Select Measure' field calculation is as below:

      case [Parameters].[Select Measure]

      when 'Sales' then sum([Sales])

      when 'Profit' then sum([Profit])

      when 'Shipping' then [Shipping 1 wk delay]

      end

       

      Attached sample report. Please help to sort by Select Measure

      Best Regards,

      Rajini

        • 1. Re: Sorting issue with LOOKUP function
          Rajini S

          Hi,

           

          Anyone have idea how to resolve sorting issue by calculated measure?

           

          Best Regards,

          Rajini

          • 2. Re: Sorting issue with LOOKUP function
            Tracy Rodgers

            Hi Rajini,

             

            It's not possible to sort by a table calculation--they're highly dependent on the view. One way to get the view to sort as desired, would be to hover over the Select Measure axis and click the desired sort button. However, when a different measure is selected, this will need to be done again.

             

            Hope this helps a bit!

             

            -Tracy

            • 3. Re: Sorting issue with LOOKUP function
              Jim Wahl

              Hi Rajini,

               

              There is workaround that allows automatic sorting by a table calculation.

               

              1. Add a second copy of the table calc pill to the view. In your example I added a copy of Selected Measure to the Columns shelf.
              2. Convert this pill to discrete.
              3. Move the pill just to the left of the dimension you want to sort. In your case left of Country, right of Week.
              4. Unselect Show Headers on the Selected Measure pill.

               

              The default Tableau sort behavior is to sort by the discrete pills: Week then Selected Measure then Country. Note that you can't change the sort order of a table calc field, but if you want the countries sorted from largest to smallest, just create a new calculated field Selected Measure Sort = -1 * Selected Measure.

               

              Since each discrete pill creates panes and borders around the panes, you'll want to select from the top menu Format > Borders > Columns  and move the Column Divider > Level one notch to the left.

               

              2013-08-30 19-31-49.png

               

              Jim

              • 4. Re: Sorting issue with LOOKUP function
                Rajini S

                Hi Jim,

                 

                Excellent solution. It is resolved my issue. Thanks once again.

                 

                Best Regards,

                Rajini