Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm producing a report that details the last payment made by a customer.
My tables are:
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:
The unique ID for transactions increases numerically in chronological order so this may be useful for a solution.
I'd solve it in the script by adding a "last transaction indicator".
Something like:
JOIN (Transactions)
LOAD max([UniqueID],
1 As LastTransaction
Resident Transactions
Group by Customer ID,
[Transaction Type]
This will result in a 1 in the LastTransaction column for the last transaction of each type made by the customer. All other records will have a NULL value in that column.
I'm assuming your UniqueID is an incremental number (so the most recent transaction has the highest UniqueID).
Hi,
if you have this new field in your script created
"(if(Transaction Type = 'Payment', max(TransactionDate)) as [Last Payment Date]"
you can use this expression in the chart:
Sum({<[Translation Type] = {"Payment"} , TransactionDate={"$(=Only([Last Payment Date]))"}>} NetTransactionAmount)
Greetings from Munich
Martina
dick
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.
Martina
That's worked, many thanks
Hi,
I think there is something wrong, that's not normal! I thought, you just have the field Maxdate in your script entered! I gave you only a possible expression!
Greetings from Munich
Martina
Hi Maria,
Your solution worked. My other comments were in response to dickzeeman's suggestion.
FINE!