Custom sort ordering - order by top N but leave "other" at bottom

Nicholas_Campbell
3 yrs ago
4replies
Answered

I have a table which shows the top N brokers, and then has the rest in an "other" bucket.. this works brilliant but I need to sort by the top N by premium but leave the "other" bucket at the bottom of the top N... is this possible?

Thanks

Nick

- imbarmarinescubar_pyram
- 3 yrs ago
- Reported - view
Hi Nicholas Campbell

I'd tried reading the questing a couple of times but I'm still not sure i understood all the details.

Could you maybe add some images or more detailed explanations?

What are the attributes, members and measures in here?
I guess that brokers is an attribute.
Premium is a measure? is it used to define the top/bottom N? and the sorting? what is a "bucket"? a custom member?

Thanks,
Imbar
- imbarmarinescubar_pyram
  
  3 yrs ago
  
  Reported - view
  I'll attach here an example from the start of what I think you meant using states instead of brokers and sales instead of premium.
  Let me know if it worked for you or if you meant something else.
  
  1. create a custom set of the top 10 (or N using a parameter) states (i called it top states)
  
  2. then create a custom member (under the state attribute) to aggregate all states not in the set (i called it others)
  
  3. add the set and the custom member with the sales to the grid:
  
  there is no need to use filters or sorts in the discovery, the set will automatically be ordered before the custom member.
- Nicholas_Campbell
- 3 yrs ago
- Reported - view
Hi

I'm sorry perhaps not explained it very well. I've got the Top N Brokers In a list, alongside the "Other" Brokers as you have demo'd above, however if I apply a sort by premium value, the "other" element gets sorted along with the "Top N".

I really want to be able to sort Top N first and leave the "Other" at the bottom of table even though the value is significantly larger than some of the Top Brokers.

I understand by default it sorts fine, with other at bottom but as I change the Top N the sort order by the Top N Brokers then lose the correct sort order
- "making the sophisticated simple"
- AviPerez
- 3 yrs ago
- Reported - view
Imbar's suggestion is how to resolve your issue.

You need to construct a list/set of the top N items - sort that as you want with the List builder (you can use any element in the model to drive the set, regardless of what you can visibly see in the report). Then independently add up all the others.

Finish, by showing the 2 elements as needed.