Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I have a table with these dimensions
Project, Vendor, Customer, Item, Status
and BidAmt as expression.
Table:
LOAD * Inline [
Project, Vendor, Customer, Item, BidAmt, Status
1A, 2, B, No1, 10, Win
1A, 2, B, No2, 3, Win
1A, 2, D, No3, 12, Lost
1A, 2, A, No4, 7, Lost
1A, 5, B, Gl4, 6, Win
1A, 5, B, Gl5, 1, Win
1A, 5, A, Gl6, 8, Lost
1A, 5, D, Gl4, 7, Lost
1A, 8, A, Dw1, 10, Lost
1A, 8, D, Dw6, 9, Lost
1A, 8, B, Dw6, 9, Win
2C, 2, D, No7, 15, Win
2C, 2, E, No6, 8, Lost
2C, 2, E, No4, 6, Lost
2C, 4, F, Kn6, 9, Lost
2C, 4, D, Kn8, 13, Win
2C, 4, E, Kn8, 13, Lost
2C, 4, D, Kn3, 3, Win
2C, 4, F, Kn4, 15, Lost
2C, 6, E, Th2, 25, Lost
2C, 6, F, Th2, 22, Lost
2C, 6, D, Th6, 17, Win
];
I need to find out the highest bid value of a project per vendor unless it has been won by a customer.
I've used this statement to get the highest bid value
=Max(TOTAL <Project, Vendor> Aggr(Sum(BidAmt), Customer, Vendor, Project))
but I just can't get the winning value
I've tried using IF statement but didn't worked the way I wanted to.
I get this:
But would like to show this:
Any help would be grantly appreciated.
Thanks.
Robert
Try this expression:
=Max({<Status = {'Win'}>}TOTAL <Project, Vendor> Aggr(Sum({<Status = {'Win'}>}BidAmt), Customer, Vendor, Project))
Try this expression:
=Max({<Status = {'Win'}>}TOTAL <Project, Vendor> Aggr(Sum({<Status = {'Win'}>}BidAmt), Customer, Vendor, Project))
Hi Sunindia,
That exactly the statement I needed
Thx a lot
Robert
No problem Robert
I am glad I was able to help.
Best,
Sunny
I was too fast on my response.
Your statement is good as long as there is a winner but it wont show projects in pending state
So I added 'Pending' to the set analysis you've provided to gain the right result.
Thx again, you've been a great help showing the way
Robert
Your sample did not have Pending Status and you did not mention that you wanted to include pending, so it was not possible for me to know
But I am glad you were able to figure it out.
Best,
Sunny