Qlik Community

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 
5112Vendor 1 3,000.00
5112Vendor 2 20,000.00
5112Vendor 3 25,000.00
6113Vendor 1 34,560.00
7889Vendor 1 56,000.00
9887Vendor 1 670,000.00
6113Vendor 2 34,520.00
9887Vendor 2 120,000.00
7889Vendor 2 3,000.00
5112Vendor 4 75,000.00
7889Vendor 3 10,000.00
9887Vendor 3 14,000.00
6113Vendor 3 98,760.00
3456Vendor 1 34,560.00
6113Vendor 4 100,000.00
7889Vendor 4 2,000.00
4562Vendor 1 456,890.00
6113Vendor 5 2,000.00
5112Vendor 5 60,000.00
3452Vendor 1 23,480.00
9870Vendor 1 18,000.00
9870Vendor 2 26,000.00
3452Vendor 2 35,000.00
6113Vendor 6 5,000.00
9887Vendor 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
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
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
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

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

text box

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

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

Community Browser