1 Reply Latest reply on May 22, 2013 1:24 AM by Jim Wahl

    I am trying to sort by one field and then within that 2nd field and sort by 2 fields is not working

    Pratima Prabhu

      I have 2 numeric fields in the report which are placed in different order in the report. I want to sort by the 1st field and 10th field which have both integer data but its not working. Please let me know how to get this to work.

        • 1. Re: I am trying to sort by one field and then within that 2nd field and sort by 2 fields is not working
          Jim Wahl

          You'll probably want to create a new field that is your sort order.

           

          You could just concatenate the two fields or, since they're integers, multiple one by 1000 then add the second.

           

          This is more difficult when you want to sort ascending by one field and descending by another. In this case you can create a rank for each field, based on a table calc, and combining these into a sort order field.

           

          For example. In the attached, I created two rank fields:

               Rank by sales=

          IF SUM([Sales]) == LOOKUP(SUM([Sales]), -1)

          THEN PREVIOUS_VALUE(0)         

          ELSE PREVIOUS_VALUE(0) + 1                      // for row 1, where there is no prev val, this evaluates to 0 + 1

          END

           

          Rank by time to ship =

          IF MAX([Time to Ship]) == LOOKUP(MAX([Time to Ship]), -1)

          THEN PREVIOUS_VALUE(0)

          ELSE PREVIOUS_VALUE(0) + 1

          END

           

          Now the sort order field = [Rank by Time to Ship] * 100000 + [Rank by Sales]

           

          Right-click on Sort Order > convert to discrete. Now you can add this to the view, before the dimension you want to sort. And then adjust the table calc compute using to change the sort order. Right-click on the Sort Order pill > Edit Table calculation. You'll see a pull down menu at the top with the Rank by fields. For each of these, select Advanced in the compute using and then Sort by. Select the appropriate sort by for each field.

           

          After the sort order is correct, you can hide the column by unchecking "Show header" on the pill.

           

          See attached. ...

           

           

          Jim