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 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]))
May be like this
Script:
Table:
LOAD *,
AutoNumber([Item ID]&[Vendor name]) as Key;
LOAD * INLINE [
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)
If you are able to solve this, then it will solve my problem. I was given sample data based on my issue.
I am expecting this amount in text box.
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?
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?
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.
So you want 10 text boxes?
Yes
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.