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.
Re: Obtaining last field based on max(date) in same line
I tried that solution but with nearly 2 million rows of data in the transactions table the script is taking a long time to execute. So far 30 minutes has elapsed as opposed to the usual 6 minutes and it's still not finished so I don't think this is a workable solution but thanks for your suggestion.