Find Wash-out sales for given set of transactions

[sanket277]

I have a requirement in which I want to compute if a 'Amount' for a particular 'Group' is washed out (nullified) or not. I tried to use tiles and joins to create a solution; but still not able to arrive at something robust. As an output, I basically would like to compute a 'Wash Sale?' column as shown in the attached spreadsheet with values like Yes/No.

Please refer the attached spreadsheet for an actual example. It would be great if someone could help me out. 

[Tyro_abc]

Hi @sanket277 

This is what I did using Multi Row formula tool, basically by checking rows above and below and comparing their amount. 

Please let me know if you have any question. 

[patrick_mcauliffe · Accepted solution]

Hey @sanket277 

Generally its best to show what you've tried first, so we aren't heading down a path you've already ruled out (and its better for hands-on learning).

That being said, I would sort them by group, then do a Running Total (Transform Tools --> Running Total)  with the total grouped by the 'Group'.

After that, I'd drop in a Tile Tool, leaving them sorted by Group and set for uniqueness on Group (so you get a sequence number for each to determine first, last, total wash, periodic wash, etc). 

[sanket277]

Thanks @Tyro_abc! But, if you compare the result of the 'Multi Row formula' with the column 'Wash Sale?' in my dataset, they don't align for the Group 'MNO' with the amounts '40' & '-40' (Please see the attachment)

[Tyro_abc]

I got it, this is because cancellation did not happen right after the transaction. For this logic to work, cancellation has to be right after that transaction.  In this case, 2 positive transactions were followed by two negative transaction. Now, it could 3 followed by 3 or n positive followed by n negative transactions.  You might have some other fields like "reference transaction#" which needs to be used to group a pair of transaction.

Regards

Arundhuti

[sanket277]

Thanks @patrick_mcauliffe for the solution. I am sorry I'll remember to attach the workflow from the next time. I did however try to incorporate your suggestion and got the workflow working with few tweaks. Please find attached solution.

Thanks again for your help, greatly appreciate it! 🙂

[sanket277]

Thanks @Tyro_abc for the inputs. I changed the workflow as per the suggestions from by @patrick_mcauliffe and got it working.

Thanks a lot guys! Happy New Year! 🙂

[patrick_mcauliffe]

@sanket277 Awesome!