find row with maximum date within a group

Alan Bennett

Alan Bennett

I managed to solve the problem by applying a filter using the MAX function :

"Person Work Permit"."Expiration Date" = MAX("Person Work Permit"."Expiration Date" by "Person Details"."Person Number") on the Expiration Date field

Richard Kemp-Eyre

Hi Alan,

I have similar requirements for DBS Checks and Work Permits, but I use RANK instead of MAX. I have a calculated column called 'Rank of Renewal Date' with the following formula:

RANK("Workforce Management - Documents of Record Real Time"."Document of Record Details"."PER_DOCUMENTS_OF_RECORD_DFF_RENEWAL_DATE_" BY "Worker"."Person Number")

I then use this column in the Analytic Filter as shown in the screenshot below.

MandeepGupta

Hi Alan,

Welcome to Oracle Analytics Community.

Can you please elaborate your query? And share the product details where you want to get this data? A detailed question helps the community members to understand the requirement and answer appropriately.

Thanks.

Alan Bennett

Hi MandeepGupta

Thanks for responding

I have an OTBI report that is returning rows containing the "Person Details.Employee Number" and "Person Work Permit.Expiration Date" from the WFM- Person Real Time subject area.

I need to be able to return a single row for each person that contains the latest expiry date.

I have seen solutions on here involving use of the max function in a new column such as MAX(

"Person Work Permit.Expiration Date" by "Person Details.Employee Number") and adding a filter where the value retuned in the new column is equal to the value in the Expiration date column

But this doesn't seem to be working

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/65200#Comment_65200

Thanks for sharing the details, Alan. What issue you are facing with this approach? Is it not giving any data or gives incorrect data?

Thanks.

MandeepGupta

https://community.oracle.com/products/oracleanalytics/discussion/comment/65208#Comment_65208

Thanks for the update, Alan. What was the issue previously? Just curious to know.

Thanks.

Alan Bennett

The method involving creating a new column containing the MAX function to compare against, produced null values for some rows and is clunky in comparison to using the max function directly in the filter, it is similar to the ROWNUMBER() over partition method when in SQL