Select Max Value from a list with condition

rviel_dubo — Mon, 29 Jun 2015 13:41:38 GMT

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

Re: Select Max Value from a list with condition

sunny_talwar — Mon, 29 Jun 2015 13:46:14 GMT

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

rviel_dubo — Mon, 29 Jun 2015 14:06:03 GMT

Hi Sunindia,

That exactly the statement I needed

Thx a lot

Robert

Re: Select Max Value from a list with condition

sunny_talwar — Mon, 29 Jun 2015 14:08:08 GMT

No problem Robert

I am glad I was able to help.

Best,

Sunny

Re: Select Max Value from a list with condition

rviel_dubo — Mon, 29 Jun 2015 14:27:08 GMT

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

sunny_talwar — Mon, 29 Jun 2015 14:29:13 GMT

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

topic Re: Select Max Value from a list with condition in QlikView

Select Max Value from a list with condition

Re: Select Max Value from a list with condition

Re: Select Max Value from a list with condition

Re: Select Max Value from a list with condition

Re: Select Max Value from a list with condition

Re: Select Max Value from a list with condition