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 got the solution.

[MK_QSL]

Can you close the thread by selecting correct answer?

[#4]

Can you clarify,why you have taken number 4 after spend in your formula.

[#5]

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.

[MK_QSL]

Check below link.. This is for Rank function..

https://help.qlik.com/en-US/qlikview/12.1/Subsystems/Client/Content/ChartFunctions/RankingFunctions/...

[#7]

stalwar1‌

For me, it is very difficult, can you solve this?

Thanks in advance

Regards

Nagarjuna.

[MK_QSL]

What you are looking for now?

[sunny_talwar]

You have some of the best people helping out here... what is that you are unable to achieve? What is the desired output?

[#10]

   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.

[MK_QSL]

Is that any criteria for ItemID?

Or you need only for 5112 every time !