Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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.

47 Replies
MK_QSL
MVP
MVP

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

sunny_talwar

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)

Capture.PNG

Not applicable
Author

If you are able to solve this, then it will solve my problem. I was given sample data based on my issue.

Not applicable
Author

I am expecting this amount in text box.

Not applicable
Author

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?

sunny_talwar

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?

Not applicable
Author

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. 

sunny_talwar

So you want 10 text boxes?

Not applicable
Author

Yes

sunny_talwar

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.