Most advanced candidate report

Summary

We have designed a report to provide the details of the most advanced candidate. The report pulls the candidate's step and status and also the candidate's name, need date

Content

Report currently pulls the most advanced candidate for all open requisitions. The report pulls the step and status of the candidate and the name of the candidate. However, we would like to pull the date the candidate entered that status but we are having some difficulties. For the candidate name we used this formula, but it doesn't work for the date:

 

MAX(CASE WHEN Rank(CAST(Replace(concat(CAST("Submission CSW Status - Historical"."Historical Step Sequence" AS CHAR(5)),CAST("Submission CSW Status - Historical"."Historical Status Sequence" AS CHAR(5))),' ','') AS Integer) BY "Requisition Identification"."Req. Identifier")=1 THEN "Candidate Identification"."Name" ELSE '' END BY "Requisition Identification"."Req. Identifier")