Multi Row Formula or something similar in nature

I am working through finalizing a process to determine which transactions on an account we are writing off.

I have looked at all financial transactions that are revenue in nature. I have sorted the data in descending order and done a running total on this. Ultimately I am looking to grab all transactions that are within - a certain dollar value from their account balance and then I can do an adjustment for any differences.

Any suggestions on a more efficient way of tackling this would be awesome.

Was thinking something like

If row 0 (running total) <= then INCLUDE="YES"

If row 0 + 1 (running total) <= then INCLUDE = "YES"

There is however a chance that there are multiple times in an account where the running balance is within the right parameters but really want the first instance this happens and include all transactions up to that point without going over (unless the following transaction is a significant portion of the balance of the account)

We have tried to grab the date that belongs to that first transaction however there could be other transactions on the same date that we do not want brought into the final dataset.

Datasets

Optimization

Data Investigation

Accepted answers

andyuttley

hey @jnans

Had to make a few assumptions at the exact challenge/question here, but hopefully this is what you're after. The 'rules' I've assumed to give you an example workflow are:

a) every account has an overall [Account_balance], assumed as the sum of all line items

b) there is a running total at an account level, which shows a current balance of each account

c) you want a multi row to find the first instance that a current balance (b) is >= overall balance (a). This instance should be marked [INCLUDE]="NO", and all other records should be "YES", even if records further down the table match the criteria for NO

- this should result in a maximum of 1 or 0 "NO" records per account

Re the messages above, some additional context would be useful, so perhaps this isn't exactly what you meant; hopefully this either gives you a solution or something you can tweak to fit your specific challenge. Shout if not though!

Thanks

Andy

AccountMultiRow.yxzp

All comments

andyuttley

Hi @jnans

Do you have a dummy data set (even just a few rows for 2 accounts) and a desired output you can provide?

BenMoss

Hi @jnans , it's difficult to fully contextulise the problem without some sample data; are you able to possible share a small sub-set of data and your desired expected result too?

Then we can play a connect the dots exercise 😄

Ben

andyuttley

hey @jnans

Had to make a few assumptions at the exact challenge/question here, but hopefully this is what you're after. The 'rules' I've assumed to give you an example workflow are:

a) every account has an overall [Account_balance], assumed as the sum of all line items

b) there is a running total at an account level, which shows a current balance of each account

- this should result in a maximum of 1 or 0 "NO" records per account

Thanks

Andy

AccountMultiRow.yxzp

jnans

The data set is similar to what I was looking at. Conceptually, this is close to what I need, I just need to make some tweaks and deal with accounts. This will help in one of the categories I end working with so thank you.

jnans

@andyuttley

Thank you again for helping me through the multi row aspect of this. I did have to tweak for my use but it gave me a direction to follow in. I wanted to provide you with the full image of what I was working on this with as the accounts I am working on writing off are quite complex in nature with many moving components to think about.

I have highlighted the sections where I used the information you had provided for the multi row formula.

Speed File Capture.PNG

Thank you again for your suggestion.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703