4 Replies Latest reply: Oct 6, 2011 11:52 AM by Alex Botten RSS

    Calculate the total spend of top N vendors, by vendor category

      I'm currently working on a problem that has been taxing me for a while.


      I am trying to produce a stacked bar chart showing the % spend of the top 10 vendors, % spend of the next 10 vendors and the remaining spend, by vendor category.

       

      I can calculate the spend of the top N vendors but I have to include the vendor as a dimension in the chart, which defeats the purpose of what I am doing.

       

      • Top 10 =IF(aggr(rank(sum([NET_Amount])),vndProclass_Lvl1,vndVendor_Name)<11,Sum([NET_Amount]))

       

      • Next 10 =(IF(aggr(rank(sum([NET_Amount])),vndProclass_Lvl1,vndVendor_Name)>10,IF(aggr(rank(sum([NET_Amount])),vndProclass_Lvl1,vndVendor_Name)<=20,Sum([NET_Amount]))))

       

      • Remaining = IF(aggr(rank(sum([NET_Amount])),vndProclass_Lvl1,vndVendor_Name)>20,Sum([NET_Amount]))

       

       

       

       

      What I actually want is to have category as the only dimension and then three expressions, one for each bar segment but my current expressions rely on vndVendor_Name being a dimension.

       

      E.g. Category | Total spend for top 10 suppliers | Total spend for next 10 suppliers | Remaining spend

       

      Can anyone help me with this? It's really beginning to bug me.