Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 !
I don't want to write vendor names also
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.
I need this amount 1,98,760, it comes from item ID 6113, vendor 3 and vendor 4 (i.e. 100,000+98,760).
How you want this output?
Text Box, Pivot Table, Chart?
Can you provide final output you are looking for?
text box
So you want for only one Item ID?
or for all?
Use this..
=SUM(Aggr(IF(Aggr(Rank(SUM(Spend),4),[Item ID],[Vendoe name])<=2,SUM(Spend)),[Vendoe name],[Item ID]))
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)
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]))