# Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

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

1 Solution

Accepted Solutions
MVP

## 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 !

47 Replies
Not applicable

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

I don't want to write vendor names also

MVP

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

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

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

MVP

## 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?

Not applicable

text box

MVP

## 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?

MVP

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

Contributor II

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

MVP

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