5 Replies Latest reply: Aug 7, 2015 5:25 AM by Kieu Nhi Nguyen RSS

    Max function

      Hi All,


      I have a set of projects which have issues logged against them. Each of the Issues can have multiple Issue Update Comments.

      I would like to be able to evaluate the 'Date of Update' column and display the newest 'Issue Update Comment', highlighted below in blue.


      I understand that this can be done using the 'max' function on the 'Date of Update' field. Unfortunately I am having some difficulty implementing this.

      I have been able to successfully retrieve the latest update date using the below function in the script (and grouping by Issue ID):

           max(DateOfUpdate) as IssueUpdateLatest


      However I am unable to return only the latest update which results in duplicate records in any tables created.


      My data looks like this:


      ProjectIssueIssue IDIssue Update IDIssue Update CommentDate of Update
      AlphaFunding Issue100101Recently identified an issue with  the funding01/02/12
      AlphaFunding Issue100102Have spoken to John and am working on fix15/02/12
      BravoResource Issue110111Resource probs01/03/12
      BravoResource Issue120121Resource probs update12/08/12
      CharlieBranding decision130131Logo decision not made28/08/12


      Any help would be much appreciated.


      Many thanks,


        • Re: Max function
          Jason Michaelides

          Beware of performing the Max() in the script as it will ignore any user-selections.


          In a straight-table chart, with all of the fields in your table above as dimensions, add this expression:


          Max(TOTAL <Project> [Date of Update])


          This will show you the date of the latest update for each project (just to demonstrate the logic).  Now, in the dimension tab, expand the + next to Issue Update Comment and double-click "Text Color".  In the expression box, write:


          IF([Date of Update]=Max(TOTAL <Project> [Date of Update]),Blue(),Black())


          Hope this helps,



          • Re: Max function

            LOAD Project,
            [Issue ID],
            [Issue Update ID],
            [Issue Update Comment],
            [Date of Update],
            [Date of Update]&'_'&Project as _key
            ooxml, embedded labels, table is Feuil1);

            Load Project as ID,
            max(date([Date of Update],'DD/MM/YYYY')) as maxdate
            Resident table
            group by Project;

            Let ID= fieldvalue('maxdate',fieldindex('ID','')) ;

            maxdate&'_'&ID as _key,
            Resident Mytable;

            Inner join
            LOAD *
            Resident table;

            drop table table, Mytable ;