2 Replies Latest reply on Dec 31, 2018 1:08 PM by Ellen Yeung

    End-User Selects Sort By and Top N

    Ellen Yeung



      I'm stuck on this and would appreciate some help.


      Must Haves:

      •    The End-User can select whether to Sort the Grand Total by Ascending or Descending per region.
      •    The end-user can select what the Top or Bottom N should be, e.g. Top 5 or Bottom 10, etc.
      •    Do not list the same State in separate rows.



      •   Show the Ranking
      •   Show the Grand Total


      I'd also like the end user to select what the metric is too (not just Profit, but also Sales, etc.  but I think once the above is solved, I can create a separate calculated field on my own.)


      Attached is a workbook with Superstore data.



        • 1. Re: End-User Selects Sort By and Top N
          Patrick Van Der Hyde



          are you trying to sort on the total for a State across all of the categories ore on a selected category as well?


          Another question - are you trying to limit the view with Index in the sorts such that when you choose ascending and let's say 5 values to show - that the bottom five States will show for the total across all categories or just to sort the view so that now 1 is at the bottom and 5 is at the top-  so the same states as the Desc view will appear in the Asc view?


          Sorting by the total is what I assume and you nearly have it with the current version.  See the attached updated version with the sort controlling if the view shows the bottom 5 states for each region or top 5 states for each region.


          I hope this gets you going.



          • 2. Re: End-User Selects Sort By and Top N
            Ellen Yeung

            Hi Patrick,


            This is great, I've learned to place the calculated field [sort test] over the Details mark.


            Yes, this is what I was looking for: rank by the total across all categories per state, and the top 5 need not be the same as the bottom 5, just the top or bottom states per region. I guess I should've used a quantity rather than a percentage in the example to make things more clear about wanting to rank by totals across all categories.


            Question: Would you know how to also show the Grand Total per row? This would help the user understand that ranking is by the total across categories.  Because we've used Index, if I select Analysis > Totals > Show Row Grand Totals, the error message says, "Cannot Turn on Grand Totals for discrete measures".  Is there some way of going around this by perhaps using a parameter instead of Index?