Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
I got the solution.
Can you close the thread by selecting correct answer?
Can you clarify,why you have taken number 4 after spend in your formula.
Here you are taking 3rd top 2 vendors spend, it might be for 3rd or 4th item.
Eg:
Item ID Total item spend Top 2 vendors spend
6113 1,00,000 80,000
5883 90,000 60,000
4886 85,000 75,000
If i take this formula
=Max(Aggr(SUM(Aggr(IF(Aggr(Rank(SUM(Spend),4),[Item ID],[Vendoe name])<=2,SUM(Spend)),[Vendoe name],[Item ID])),[Item ID]),3)
for item 3( based on spend amount) it is showing 60,000. But actual amount is 75,000.
Thanks and Regards
Nagarjuna.
Check below link.. This is for Rank function..
What you are looking for now?
You have some of the best people helping out here... what is that you are unable to achieve? What is the desired output?
This is my sample data. I have changed some data for convenience.
Item ID | Vendor name | Spend |
9887 | Vendor 1 | 670,000.00 |
9887 | Vendor 2 | 120,000.00 |
9887 | Vendor 3 | 14,000.00 |
9887 | Vendor 4 | 12,556.00 |
9870 | Vendor 2 | 26,000.00 |
9870 | Vendor 1 | 18,000.00 |
7889 | Vendor 1 | 56,000.00 |
7889 | Vendor 3 | 10,000.00 |
7889 | Vendor 2 | 3,000.00 |
7889 | Vendor 4 | 2,000.00 |
6113 | Vendor 4 | 100,000.00 |
6113 | Vendor 3 | 98,760.00 |
6113 | Vendor 1 | 34,560.00 |
6113 | Vendor 2 | 34,520.00 |
6113 | Vendor 6 | 5,000.00 |
6113 | Vendor 5 | 2,000.00 |
5112 | Vendor 4 | 75,000.00 |
5112 | Vendor 5 | 140,000.00 |
5112 | Vendor 3 | 25,000.00 |
5112 | Vendor 2 | 20,000.00 |
5112 | Vendor 1 | 3,000.00 |
4562 | Vendor 1 | 456,890.00 |
3456 | Vendor 1 | 34,560.00 |
3452 | Vendor 2 | 35,000.00 |
3452 | Vendor 1 | 23,480.00 |
Desired output:
I need top 2 vendors spend for a particular item ID. Take an example of item ID 5112. Total amount what i need is 215,000 (75000+140000), these amounts should come based on spend for a particular item. Item ID 5112 is 4th spend item. I don't want to write 5112 or vendor name(i.e. in this case vendor 4 or vendor 5) in final formula.
Thanks in advance
Regards
Nagarjuna.
Is that any criteria for ItemID?
Or you need only for 5112 every time !