Total of Top n ...

[#1]

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

[#2]

Hi Ori

Any luck?

I think this technique will help me quite often, but I have not yet figured it out.

Oli

[orital81 · Accepted solution]

Hi Oli

There is a way to perform this task with pivot table, using a small script addition.

Here is the action plan:

1. Create a table that will hold to sum of top 10 suppliers for every month.

    it will have year and total top 10 value.

2. in the chart expression you will use this value as following:

    sum({$<[Receipt Year] = {$(vCurrentYear)} value) / sum({$<[Receipt Year] = {$(vCurrentYear)} total top 10 value)

In order to create the new table use something like this:

     Sum(If(aggr(rank(sum([Currency line value])), MstrSupplierName) <= 10, [Currency line value], 0)) as [total top 10     value],

     Year As YearMonth

Resident YourTable

group by Year

Im sure there are other good ways to do it, such as using a top 10 flag or you can calculate the top 10 in a variable

but this approach will work.

Let me know how it goes for you

Good Luck

[michael_maeuser]

sorry here it is

[#5]

Hi,

Please try with  the firstsortedvalue