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.