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.
Solved! Go to Solution.
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.
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).
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)
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
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! 🙂
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! 🙂
@sanket277 Awesome!