2 Replies Latest reply on Aug 31, 2017 8:10 AM by Jim Dehner

    Pick Maximum date for the record

    Shantam M
      Report NameAccess Date
      Report 131-Aug
      Report 130-Aug
      Report 129-Aug
      Report 128-Aug
      Report 231-Aug
      Report 230-Aug
      Report 229-Aug
      Report 228-Aug
      Report 331-Aug
      Report 330-Aug
      Report 329-Aug
      Report 328-Aug

       

       

      My data looks something like the above. Each report is accessed on some dates.

      What I want to do is to pull the last access date that the report was used i.e. max(Access_date).

       

      I was successful in creating a new CALC FIELD of max(Access_date) as "Last_Access_Time" but when I dropped the pill in the bar and tried doing the YEAR(Last_Access_Time). It failed.

      It wasn't even showing the options of a date field such as YEAR, QUATER, MONTH etc which usually datefield columns have.

       

      My alternative approach -  Can I rank the dates in descending order and then pick the top one (rank =1)? If yes, Can any one tell me how to apply RANK function on date column ?

       

      Any help on this is appreciated