Maxstring in Set Notation

[julesjohnson]

Hello Everyone,

I am using the following statement to return the highest dollar amount of the ticket with a status of *open*, and a request type of *Incident* and it works just fine. 

=Num(MaxString({$<[Request Type_S]={'*Incident*'},[Request Status_S]={'*open*'}>
} [CM_Cost/Hr]*[Elapsed_Time_S]),'$#,##0')

Now I want a statement that returns the corresponding “Request ID”.  I have this statement so far but need to also include where the dollar amount is maxstring (CM_Cost/Hr]*[Elapsed_Time_S]). Is there a way I can do this?

  =only({$ <[Request Type_S]={'*Incident*'},[Request Status_S]={'*open*'}>
} [Request ID_S])

[sunny_talwar · Accepted solution]

May be you need this

=FirstSortedValue({$<[Request Type_S] = {'*Incident*'}, [Request Status_S] = {'*open*'}>} [Request ID_S], -[CM_Cost/Hr] * [Elapsed_Time_S])

[julesjohnson]

Wow, that worked!!

Thanks Sunny