Select Max Value from a list with condition

[rviel_dubo]

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

[sunny_talwar · Accepted solution]

Try this expression:

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

[rviel_dubo]

Hi Sunindia,

That exactly the statement I needed

Thx a lot

Robert

[sunny_talwar]

No problem Robert

I am glad I was able to help.

Best,

Sunny

[rviel_dubo]

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

[sunny_talwar]

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