Forum Discussion
Search within previous 90 days
Hello all - a question regarding searching through transaction data.
I have a retail transaction data table - columns for date, customer ID, item quantity, and total revenue. It also has a flag for normal transactions versus returns for refund (1=normal, -1=return).
Sometimes customers return only a few items out of a larger basket of purchases. I'd like to identify the return transactions that appear to correspond to the full baskets - i.e. a match across each of customer ID, item quantity, and total revenue, within 90 days before the return transaction.
What would be the best way to code this conditional search? I.e. for each line in the transaction (pseudocode)
"IsFullBasketReturn"
if isNormalTransaction Is 1 Then false Else if Count (alltransactions where date is [within 90 days before thistransactiondate] AND customerID = thistransactioncustomerID AND quantity=thistransactionquantity AND totalrevenue=thistransactiontotalrevenue) = 0 Then false Else true
Thank you!
Answering this with how I got it working eventually, in case it can help others:
IsExactCancellation=
if(Transactions[IsNormalTransaction]=1,BLANK(),
CALCULATE(lastdate(Transactions[Date]),FILTER(all(Transactions),
Transactions[IsNormalTransaction]=1 && Transactions[date]>=earlier(Transactions[Date])-90
&& Transactions[date]<=earlier(Transactions[Date])
&& Transactions[CodeForExactCancellationCheck]=earlier(Transactions[CodeForExactCancellationCheck]))))With a CodeForExactCancellationCheck column generated by concatenating the various item numbers and quantities in the basket to make it a unique view of the basket content in one (long) number.
3 Replies
Hi,
Share a sample dataset and show the expected result.
Answering this with how I got it working eventually, in case it can help others:
IsExactCancellation=
if(Transactions[IsNormalTransaction]=1,BLANK(),
CALCULATE(lastdate(Transactions[Date]),FILTER(all(Transactions),
Transactions[IsNormalTransaction]=1 && Transactions[date]>=earlier(Transactions[Date])-90
&& Transactions[date]<=earlier(Transactions[Date])
&& Transactions[CodeForExactCancellationCheck]=earlier(Transactions[CodeForExactCancellationCheck]))))With a CodeForExactCancellationCheck column generated by concatenating the various item numbers and quantities in the basket to make it a unique view of the basket content in one (long) number.
