nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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.

Wash Sale dummy data.xlsx

Workflow

Common Use Cases

Best Practices

Join

Data Investigation

Accepted answers

patrick_mcauliffe

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).

All comments

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.

Washout Transaction.yxmd

patrick_mcauliffe

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)

Wash Sale Err1.PNG

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! 🙂

Washout Transaction 2.yxmd

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15907

binu_acs 15783

MarqueeCrew 13709

apathetichell 13703