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,

      Alan

        • 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,

           

          Jason

          • Re: Max function

            table:
            LOAD Project,
            Issue,
            [Issue ID],
            [Issue Update ID],
            [Issue Update Comment],
            [Date of Update],
            [Date of Update]&'_'&Project as _key
            FROM
            D:\QlikView\AXS\data\test.xlsx
            (
            ooxml, embedded labels, table is Feuil1);

            Mytable:
            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','')) ;

            projet:
            NoConcatenate
            LOAD
            maxdate&'_'&ID as _key,
            maxdate
            Resident Mytable;

            Inner join
            LOAD *
            Resident table;

            drop table table, Mytable ;