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
      Dan10/18/201812345$3000
      Sally11/14/1812345$3000

       

       

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

      NameSUM(Amount)
      Sally$3,000
      Dan$3,000

       

       

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

       

      NameSUM(Amount)
      Sally$3,000
      Dan$0
        • 1. Re: Group two rows with different data based on MAX Date
          Michel Caissie

          Peter,

           

          Without testing anything you can try the following calculations;

           

          To get the last date go with

          [LastDate]

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

           

          To know if a user gets the amount

          [UserAmount]

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

           

          And the  new amount to use in your report would be

          [AmountAdjusted]

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

           

          Michel