Solved: Re: How to get value of top 2 vendors spend for a ... - Qlik Community

Report Inappropriate Content · ‎2017-02-22

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.

MK_QSL · ‎2017-02-24

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 !

View solution in original post

Report Inappropriate Content · ‎2017-02-22

I don't want to write vendor names also

jonathandienst · ‎2017-02-22

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

Report Inappropriate Content · ‎2017-02-22

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

MK_QSL · ‎2017-02-22

How you want this output?

Text Box, Pivot Table, Chart?

Can you provide final output you are looking for?

Report Inappropriate Content · ‎2017-02-22

text box

MK_QSL · ‎2017-02-22

So you want for only one Item ID?

or for all?

MK_QSL · ‎2017-02-22

Use this..

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

Anonymous · ‎2017-02-22

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)

MK_QSL · ‎2017-02-22

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]))