3 Replies Latest reply on Nov 2, 2017 7:49 AM by Shradha Santuka

    Help sorting a Top N and Rest of/Others stacked bar chart

    Eve Richer

      Hi all,


      I'm wondering if anyone can help me with a sorting question... I have attached a package workbook so you can see what I'm working with.


      Background info: I made a chart that shows Malaysia's imports of wood products over time. The colors on the chart represent the main supplier countries Malaysia is importing from, and there is a Top N parameter so the user can decide if she wants to see the top 10 countries, or top 5, etc. All other countries get grouped into a "Rest of world" category that is colored gray. I also added some filters so the user can decide if she wants to see Malaysia's imports of all timber products, or just log imports, and so on.


      What I'm trying to do: I would like the chart to automatically sort every time the user interacts with the parameter and filters. I'd like all bars to sort by the sum of Trade Value, with the largest values on the bottom, based on the 2014 trade value (the most current year). However there is one exception! I would like the gray "Rest of world" category to always go to the top of the chart, regardless of its value. And I want to make sure that the order of the colors in the chart exactly matches the order of the colors in the legend. Does that make sense?


      Here's an example. Let's say the user wants to see the Top 3 countries and selects "sawn wood" from the dropdown menu. I would like both the chart and the legend to automatically put USA at the bottom, then Indonesia, then Thailand, then Rest of world. Below is an image to show you what I mean:



      I think the solution to my question involves nested sorting, but I tried to read about nested sorting and I don't understand how to apply other examples to my particular situation. If anyone has any suggestions I would be very thankful!