1 Reply Latest reply on Dec 20, 2018 2:19 PM by Michel Caissie

    Group two rows with different data based on MAX Date

    Peter Goett

      I am trying to look at a list of users in our system, and how many invoices they have reviewed and the amount for the invoices. On rare occasions though, an invoice gets reviewed by one user, then gets routed to another user to do a second review, causing duplicate rows per invoice. If I SUM the amount of the invoices by user, it will double count the invoice. 


      Unfortunately I can't link the workbook because there is some sensitive data, but the backend data looks like this:


      NameAction DateInvoice NumberAmount



      In my view, if I SUM the amount that they have reviewed, it will incorrectly look like Dan and Sally are +$3,000.




      I would rather only show Sally having +$3,000, since she was the most recent user to review the invoice.


        • 1. Re: Group two rows with different data based on MAX Date
          Michel Caissie



          Without testing anything you can try the following calculations;


          To get the last date go with


          {FIXED [Invoice Number ]: MAX([ActionDate])}


          To know if a user gets the amount


          {FIXED [Invoice Number ]: MIN(if [Action Date] = [LastDate] then [Name] end)}


          And the  new amount to use in your report would be


          if [Name] = [UserAmount] then [Amount] else 0 end