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

# Total of Top n ...

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

• ###### Re: Total of Top n ...

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?

• ###### Re: Total of Top n ...

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

• ###### Re: Total of Top n ...

Hi Oli

You are welcome to attach your qvw if you wan't some more help in this issue.

Ori

• ###### Re: Re: Total of Top n ...

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.

• ###### Re: Total of Top n ...

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

• ###### Re: Total of Top n ...

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

• ###### Re: Re: Total of Top n ...

see attached

• ###### Re: Total of Top n ...

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

• ###### Re: Total of Top n ...

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

• ###### Re: Total of Top n ...

Hi Ori

Any luck?

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

Oli

• ###### Re: Total of Top n ...

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

• ###### Re: Total of Top n ...

Hi,