Formula for Netting off Amounts.

[Vijay_Vittal_11]

Data: I have a General and Administrative Expenses Journal Entry export. Fields involved: Transaction No, Period, Transaction date, description, amount. 

Problem Case: I am trying to separate out netting off amounts. For example: There is transaction with description XXX - $ 10,000 and its equivalent reversal $ (10,000). I am trying to identify these netting off transactions and mark them as "Net" in a new column.

The only way to identify a transaction and its reversal is either through the description such as UBER charges $100, UBER Charges $(100) and the Transactions Number where the T.NO of original transaction is 7890023, and its reversal is 7890024.

I have tried using one of the Workflow from the community, but it marks an additional transaction that does net off. For Example, as below:

UBER Charges: $ 10,000 11/12/22

UBER Charges: $ -10,000 11/12/22

UBER Charges: $10,000 05/12/23

I don't want the third transaction to be marked as net off.

I am looking to write a formula to mark the netting off amounts in alteryx but unable to do so, please advise.

[gawa]

hi @Vijay_Vittal_11 

I propose to utilize Multi-row Formula in your case. Multi-row Formula can refer to another row data for calculation unlike general Formula tool.

In your case, I assume that "Net-off transactions occurs in 2 series transactions within the same day", and prepared workflow as attached. If this is not in line with your intention, please tell me so I can advise other workaround.

[Vijay_Vittal_11]

Thank you so much for this advise. It was helpful.