Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi Ori
Any luck?
I think this technique will help me quite often, but I have not yet figured it out.
Oli
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
sorry here it is
Hi,
Please try with the firstsortedvalue