13 Replies Latest reply: Sep 12, 2013 3:17 AM by Amuthabharathi Subramanian RSS

    Total of Top n ...

    Oliver Annells

      Hi 

      I have searched this forum but can not adapt any of the solutions and yield a correct result

       

      I have created a pivot table to show the top n suppliers.

      The Expression:

           if(aggr(rank(sum({1<[Receipt Year] = {$(vCurrentYear)}> *<[Receipt Month #] = {'<=$(vLastMonth)'}>}[# Currency line value])), [Master           supplier no.]) <= 10, MstrSupplierName, null())

      The Dimension:

           This is held in a variable called vCurrentYear_Spend

           sum({1<[Receipt Year] = {$(vCurrentYear)}> *<[Receipt Month #] = {'<=$(vLastMonth)'}>}[# Currency line value])


      I can calculate the % of Total for ALL suppliers (this is working correctly).
           =$(vCurrentYear_Spend)/sum({$<[Receipt Year] = {$(vCurrentYear)}>} total [# Currency line value])


      What I am finding difficult is how to calculate the % of Top 10 because I don't know how to write the formula to sum() the top 10.


      I hope this makes sense...


      Thanks

      Oli