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 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
If you already have the Top N Suppliers Value
You can add another expression with the value of
all suppliers for the specific year AGGR(Sum(Value),[Receipt Year])
Then all you need to do is calculate the Ratio:
TopN Value / Total Value.
Is this what you meant?
Hi Ori
Thank you for your reply. As yet I have not managed to get this to work. I think this is my limited knowledge holding me back. I am trying to simply it at the moment by removing the set analysis from the formulas...
Oli
Hi Oli
You are welcome to attach your qvw if you wan't some more help in this issue.
Ori
Hi Ori
I have attached a very simplified example file. I hope none of the complexity I have left out will affect your answer.
Once again, thank you for your assistance.
Hi Oli
There is a simple solution,
If you are using straight table you can use the Relative checkbox
which will show you the percentage.
See my attachment, I have created a new table on the right
Wow, you're fast!!
Do you know of a way to get this If I am having to use a pivot table instead of the straight version?
I am asking because this is currently the case and I am trying to learn as much as possible from my peers for future projects.
Many thanks
Oli
see attached
There is a way to do it with pivot table, but it's a bit more complicated.
Hopefully, I will get to it later on...
Good luck
Hi Michael
Am I missing something? I can not see what you are showing me in the attachment.
If there is some confusion, I am trying to figure out the formula that would result in the value 1,374,942 (the total of the top 10 suppliers).
Oli