2 Replies Latest reply on Oct 25, 2016 7:49 PM by Freddy W

    Sorting with multiple dimensions

    Freddy W



      How would you sort the table below by 2016 sales? I understand the index function when sorting with multiple dimensions. However, this method sorts based on historical sales across all years. Maybe a calculated field under the Sales measure to specifically sort 2016?


      Region      Index    Customer      2014    2015    2016

      East              1       CustomerA    $50     $100    $50

                           2       CustomerC    $50     $100    $20


      Central          1       CustomerB    $50     $60      $100

                            2       CustomerD    $50     $100    $20


      West             1       CustomerB    $50     $50     $100

                           2       CustomerC    $50     $100    $40


      South            1       CustomerA    $50     $100    $300

                           2       CustomerD    $50     $100    $40


      Thank you

        • 1. Re: Sorting with multiple dimensions
          Joshua Milligan



          You could use a calculation with code like:


          WINDOW_MAX(IF Last() == 0 THEN SUM(Sales) END)


          Make sure it is computed Table Across (specifically addressing by date) and make it discrete.  Then you can place it on Rows to control the sort order.  Place it to the left of the field at the level of header you want to sort (either Customer or Region).  You can control ascending or descending by adding a negative sign: e.g. -WINDOW_MAX(IF Last() == 0 THEN SUM(Sales) END)



          Finally, you can hide the headers for the field since you are only using the headers to control the sort and not for visual display.


          Hope that helps!


          1 of 1 people found this helpful
          • 2. Re: Sorting with multiple dimensions
            Freddy W

            Yes this worked, Thank you!