5 Replies Latest reply: Jun 29, 2015 10:29 AM by Sunny Talwar

# Select Max Value from a list with condition

Hi everyone,

I have a table with these dimensions

Project, Vendor, Customer, Item, Status

and BidAmt as expression.

Table:

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

• ###### Re: Select Max Value from a list with condition

Try this expression:

=Max({<Status = {'Win'}>}TOTAL <Project, Vendor> Aggr(Sum({<Status = {'Win'}>}BidAmt), Customer, Vendor, Project))

• ###### Re: Select Max Value from a list with condition

Hi Sunindia,

That exactly the statement I needed

Thx a lot

Robert

• ###### Re: Select Max Value from a list with condition

No problem Robert

I am glad I was able to help.

Best,

Sunny

• ###### Re: Select Max Value from a list with condition

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

• ###### Re: Select Max Value from a list with condition

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