How to get value of top 2 vendors spend for a particular item

[#1]

How to get value of top 2 vendors spend for a particular item.

This is my sample data

Item ID	Vendoe name	Spend
5112	Vendor 1	3,000.00
5112	Vendor 2	20,000.00
5112	Vendor 3	25,000.00
6113	Vendor 1	34,560.00
7889	Vendor 1	56,000.00
9887	Vendor 1	670,000.00
6113	Vendor 2	34,520.00
9887	Vendor 2	120,000.00
7889	Vendor 2	3,000.00
5112	Vendor 4	75,000.00
7889	Vendor 3	10,000.00
9887	Vendor 3	14,000.00
6113	Vendor 3	98,760.00
3456	Vendor 1	34,560.00
6113	Vendor 4	100,000.00
7889	Vendor 4	2,000.00
4562	Vendor 1	456,890.00
6113	Vendor 5	2,000.00
5112	Vendor 5	60,000.00
3452	Vendor 1	23,480.00
9870	Vendor 1	18,000.00
9870	Vendor 2	26,000.00
3452	Vendor 2	35,000.00
6113	Vendor 6	5,000.00
9887	Vendor 4	12,556.00

Here i want to get total spend of top 2 vendors based on spend (i.e vendor 4 and vendor 3) for  an item ID 6113. I don't want to write 6113 in formula, it should be automatic, it is 3rd item based on total spend.

[#2]

I am creating one slide for each item. So i can't create table.

[MK_QSL]

To be honest, you are not able to represent your requirements properly. Can you please explain what exatly you want?  Sometimes you are asking for one ID which is in Rank 3 and sometimes another ID which is having Rank 4. Now you are coming up saying that youneed 10 text boxes. Can you tell us in brief description what exactly tou are looking for. This way you will get your answer properly.  Make sure that in one go you explain all your need.

We are happy to help but without proper explanation all of us are moving in cycle. This way we can never be finished this question.

[#4]

If you are able to solve for one item, then it will be easy for me to write same formula for other items,that's the reason I was asked for one item. First I asked for item ID 6113, u replied one formula and used it in my data, it's worked perfectly for one item then I used the same formula for other items, sometimes I got the correct answer and sometimes not, then I analyzed the formula why it is showing wrong for some items,you had taken top 2 vendors spend without considering item spend i.e you are considering only top 2 vendors spend for ranking purpose, my issue is first I need top 4th item based on spend then top 2 vendors spend for that particular item. Problem with your formula is sometimes top 2 vendors spend of 4th item may be more than top 2 vendors spend of 3rd item because of having more vendors to the item 3. That's why I changed my sample data,if u try your formula to the item 5112 it won't work, that is the reason I changed my item 6113 to 5112.I have given clearly what amount i need in my text box. I think you understand what is my issue,still if you have any doubts ask me

Thank u

[MK_QSL]

OK... Based on your description...

TOP 4th ITEM is 5112..

And now you want to display top 2 vendors total

= Vendor 5 + Vendor 4

= 140000 + 75000

Correct me if I am wrong !

[MK_QSL]

If yes, then use below expression...

=SUM(

  {<

  [Item ID] = {'$(=FirstSortedValue([Item ID],  -Aggr(SUM(Spend),[Item ID]), 4))'},

  [Vendor name] = {"=Rank(SUM({<[Item ID] = {'$(=FirstSortedValue([Item ID],  -Aggr(SUM(Spend),[Item ID]), 4))'}>}Spend))<=2"}

  >}

Spend)

If you want TOP 2 Vendor Spend for 1st [Item ID] based on Spend

i.e. Vendor 1 + Vendor 2 = 790000 (For 9887)

Simply change the RED LETTER 4 with 1

So for 2nd Rank Change to 2

and so on...

[MK_QSL · Accepted solution]

If you want Vendor and Item selection should not affect the text boxes output then use below expression.

=SUM(

  {<

  [Item ID] = {'$(=FirstSortedValue({<[Item ID],[Vendor name]=>}[Item ID],  -Aggr(SUM({<[Item ID]=,[Vendor name]=>}Spend),[Item ID]), 4))'},

  [Vendor name] = {"=Rank(SUM({<[Vendor name], [Item ID] = {'$(=FirstSortedValue({<[Item ID],[Vendor name]=>}[Item ID],  -Aggr(SUM({<[Item ID]=,[Vendor name]=>}Spend),[Item ID]), 4))'}>}Spend))<=2"}

  >}

Spend)

Happy Qliking !

[shivkumar300]

Hi,

Try below method

create two variable

--->  top spend ie vmax1=max(spend)

--->  2nd top  ie vmax2=max(spend,2)

then in text box use expression as $(vmax1)+$(vmax2)

then by selecting id you can get your desired output

[#9]

Thank you so much for your patience, its working.