47 Replies Latest reply: Feb 25, 2017 1:29 AM by Shivkumar Maurya

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

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.

• ###### Re: How to get value of top 2 vendors spend for a particular item

I don't want to write vendor names also

• ###### Re: How to get value of top 2 vendors spend for a particular item

Perhaps you could show us what you would like to get as a result from your sample data. That might help to clarify your requirements.

• ###### Re: How to get value of top 2 vendors spend for a particular item

I need this amount 1,98,760, it comes from item ID 6113, vendor 3 and vendor 4 (i.e. 100,000+98,760).

• ###### Re: How to get value of top 2 vendors spend for a particular item

How you want this output?

Text Box, Pivot Table, Chart?

Can you provide final output you are looking for?

• ###### Re: How to get value of top 2 vendors spend for a particular item

text box

• ###### Re: How to get value of top 2 vendors spend for a particular item

So you want for only one Item ID?

or for all?

• ###### Re: How to get value of top 2 vendors spend for a particular item

Use this..

=SUM(Aggr(IF(Aggr(Rank(SUM(Spend),4),[Item ID],[Vendoe name])<=2,SUM(Spend)),[Vendoe name],[Item ID]))

• ###### Re: How to get value of top 2 vendors spend for a particular item

And if you want only for 6113, use as below..

=SUM(Aggr(IF(Aggr(Rank(SUM({<[Item ID] = {'6113'}>}Spend),4),[Item ID],[Vendoe name])<=2,SUM({<[Item ID] = {'6113'}>}Spend)),[Vendoe name],[Item ID]))

• ###### Re: How to get value of top 2 vendors spend for a particular item

I don't want to write the item ID number in formula.Is this possible? it is 3 rd spend item based on spend.

Regards

Nagarjuna

• ###### Re: How to get value of top 2 vendors spend for a particular item

You mean you want the sum of top 2 vendor for 3rd spend item id?

• ###### Re: How to get value of top 2 vendors spend for a particular item

=Max(Aggr(SUM(Aggr(IF(Aggr(Rank(SUM(Spend),4),[Item ID],[Vendoe name])<=2,SUM(Spend)),[Vendoe name],[Item ID])),[Item ID]),3)

• ###### Re: How to get value of top 2 vendors spend for a particular item

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.

• ###### Re: How to get value of top 2 vendors spend for a particular item

Yes

• ###### Re: How to get value of top 2 vendors spend for a particular item

I have replied.. check

• ###### Re: How to get value of top 2 vendors spend for a particular item

Firstsortedvalue will get you the vendor names;

FirstSortedValue([Vendoe name],-Spend)  //- Top Spend Vendor

FirstSortedValue([Vendoe name],-Spend,2)    //-2nd spend Vendor

Max will get you the spend;

=Max(Spend) //- Top Spend

+

Max(Spend,2) //- 2nd Spend

This will change based on selections.

To get only Item 6113

=Max({<[Item ID]={6113}>}Spend)

+

Max({<[Item ID]={6113}>}Spend, 2)

• ###### Re: How to get value of top 2 vendors spend for a particular item

Have you tried this?

=Max(Aggr(SUM(Aggr(IF(Aggr(Rank(SUM(Spend),4),[Item ID],[Vendoe name])<=2,SUM(Spend)),[Vendoe name],[Item ID])),[Item ID]),3)

• ###### Re: How to get value of top 2 vendors spend for a particular item

Not working

• ###### Re: How to get value of top 2 vendors spend for a particular item

Sorry , its working.

Thank you.

• ###### Re: How to get value of top 2 vendors spend for a particular item

I have a doubt,why you have taken number 4 after spend in your formula.

• ###### Re: How to get value of top 2 vendors spend for a particular item

Now i want total spend of item 6113.

• ###### Re: How to get value of top 2 vendors spend for a particular item

I got the solution.

• ###### Re: How to get value of top 2 vendors spend for a particular item

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

• ###### Re: How to get value of top 2 vendors spend for a particular item

stalwar1

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

Regards

Nagarjuna.

• ###### Re: How to get value of top 2 vendors spend for a particular item

What you are looking for now?

• ###### Re: How to get value of top 2 vendors spend for a particular item

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

• ###### Re: How to get value of top 2 vendors spend for a particular item

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.

Regards

Nagarjuna.

• ###### Re: How to get value of top 2 vendors spend for a particular item

Is that any criteria for ItemID?

Or you need only for 5112 every time !

• ###### Re: How to get value of top 2 vendors spend for a particular item

If you are able to solve this, then it will solve my problem. I was given sample data based on my issue.

• ###### Re: How to get value of top 2 vendors spend for a particular item

If you want this result in straight table or pivot table...

Dimension

Item ID

Expression

=SUM(Aggr(IF(Aggr(Rank(SUM(Spend),4),[Item ID],[Vendor name])<=2,SUM(Spend)),[Vendor name],[Item ID]))

• ###### Re: How to get value of top 2 vendors spend for a particular item

I am expecting this amount in text box.

• ###### Re: How to get value of top 2 vendors spend for a particular item

May be like this

Script:

Table:

AutoNumber([Item ID]&[Vendor name]) as Key;

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"

];

Dimension

Item ID

Expression

=Sum({<Key = {"=Aggr(Rank(Sum(Spend)), [Item ID], [Vendor name]) < 3"}>}Spend)

• ###### Re: How to get value of top 2 vendors spend for a particular item

I have a data of almost 50,000 rows and 150 columns, i prepared all charts, I don't want to load data again. I need only final amount in text box, Is any other way exist?

• ###### Re: How to get value of top 2 vendors spend for a particular item

What do you mean you don't want to load data again? You are never going to reload your application? You want the final amount in text box? for just 5112 or another Item ID?

• ###### Re: How to get value of top 2 vendors spend for a particular item

I was connected to the database, written some query and retrieved data.Now i am directly applying formulas to the data. I need the same formula for 10 Item IDs(based on spend), I wanna display it in text box.I have one doubt, I am doing all these work for a template. Why i am asking all these, is because, if i change the data,then the graphs,tables and text boxes should change automatically.My final goal is this, That is the reason, i don't want to write item ID, Vendor name etc. in final formula because different data will have different item IDs and different vendor names.

• ###### Re: How to get value of top 2 vendors spend for a particular item

So you want 10 text boxes?

• ###### Re: How to get value of top 2 vendors spend for a particular item

Yes

• ###### Re: How to get value of top 2 vendors spend for a particular item

So there will always be only 10 Item IDs? Never more never less? I mean if Item ID can go up and down, then what is the point of creating text boxes. Why not show them in a table.

• ###### Re: How to get value of top 2 vendors spend for a particular item

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

• ###### Re: How to get value of top 2 vendors spend for a particular item

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.

• ###### Re: How to get value of top 2 vendors spend for a particular item

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

• ###### Re: How to get value of top 2 vendors spend for a particular item

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 !

• ###### Re: How to get value of top 2 vendors spend for a particular item

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...

• ###### Re: How to get value of top 2 vendors spend for a particular item

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 !

• ###### Re: How to get value of top 2 vendors spend for a particular item

Thank you so much for your patience, its working.

• ###### Re: How to get value of top 2 vendors spend for a particular item

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