2 Replies Latest reply: Jul 23, 2015 9:13 AM by Andrew Houston-Floyd RSS

    Custom dimension & custom sort

    Andrew Houston-Floyd

      Community, I could really use your help.

       

      I have a requirement for a Mekko chart, but the problem probably applies to many other situations as well.

       

      First, in the chart I have to show the top 5 Company by Amounts, with all other companies placed in 'Others'. Ordinarily, I would just use the Dimension Limits option, but...

       

      Secondly, any selections in Company (1 or more) have to be dynamically taken out of 'Others' and shown next to the top 5, and displayed at the bottom of the chart in a specific color. So the chart must be sorted: selections at bottom, top 5 in middle, 'Others' at top. Both the selections at the bottom, and the top 5, must be sorted by sum(Amounts).

       

      I have a partial solution, but cannot figure out how to control the sorting. I have attached a qvw and data sample for you.

       

      Currently, my custom dimension is the following:

       

      =aggr(if(SubStringCount(Concat(Distinct GetFieldSelections(Company)), Company)>0,Company,

      IF(RANK(SUM({<Company=>} Amounts)) <=5, Only({1}Company),'Others')),Company)

       

      and my current sort expression sorts selections to the bottom, but can't control remaining values:

       

      if(SubStringCount(Concat(Distinct GetFieldSelections(Company)), Company)>0,1e10,

      sum({<Company=>}Amounts))

       

      I really appreciate your help!