6 Replies Latest reply: Jul 26, 2012 10:30 AM by Martina Brenner RSS

    Obtaining last field based on max(date) in same line

      I'm producing a report that details the last payment made by a customer.

       

      My tables are:

      • Customer Table: Customer ID, Customer name
      • Transactions Table: UniqueID, Customer ID, Transaction Type, Transaction Date, Transaction Amount, Transaction Method

       

      The data I'm trying to produce is a list of customers and their last payment amount, date and method. A payment is a transaction type.

       

      I've managed to obtain the last payment amount and date but am struggling to get the method.

       

      I obtained the last payment date with the below formula in the script, loading the transaction table and grouping by Customer ID

       

      "(if(Transaction Type = 'Payment', max(TransactionDate)) as [Last Payment Date]"

       

       

       

      I then got the last payment amount with the following expression in the pivot table with Customer ID as my dimension. However I can see that there may be a flaw with this if 2 payments were to be received in the same day.

       

       

       

       

      "sum(if(TransactionDate=[Last Payment Date] and [Transaction Type]='Payment', NetTransactionAmount) "

       

       

      So my questions are:

      1. Is there a better way of getting the last payment amount?
      2. How do I get the last payment method?

       

      The unique ID for transactions increases numerically in chronological order so this may be useful for a solution.