Amount reduction and addition with condition

Hi community,

I would like to ask how do build the multi-row formula for the following condition:

1. Before using the multi-row formula, we would need to change the date from ascending order and group by product

2. To build the multi-row formula, we would need to consider the following:

A . If the document type for next row is CZ , then we need to deduct the amount with the concept : deduct the current amount against the next row

B. If the document type for next row is OP, then we need to add the amount with the concept : add the current amount against the next row

C. If the document type for the current row is AC, then the calcualtion amount is zero.\

D. If the document type for current row is OP, then the calculation amount in zero.

Many thanks

Amount Reduction.xlsx

Common Use Cases

Accepted answers

Luke_C

Hi @SH_94

Using your logic product A amounts match your desired output, however on product B your example doesn't match the logic you wrote. For instance:

Why is the 4/28 row 0? The next row is OP not CZ so not sure why it's being subtracted.
For the 10/12 row, again there's logic on if the current row is AC, but not specified what to do when the next row is AC.

Hopefully you can use this as a starting point and tweak to your needs.

New Workflow3.yxmd

All comments

Luke_C

Hi @SH_94

Using your logic product A amounts match your desired output, however on product B your example doesn't match the logic you wrote. For instance:

Why is the 4/28 row 0? The next row is OP not CZ so not sure why it's being subtracted.
For the 10/12 row, again there's logic on if the current row is AC, but not specified what to do when the next row is AC.

Hopefully you can use this as a starting point and tweak to your needs.

New Workflow3.yxmd

SH_94

Hi @Luke_C ,

Thanks for the correction,i realise that it is because i havent input another condition as below:

4. If the document type for current row is OP, then the calculation amount in zero.

Can you assist to include this formula to check whether it work now?

Many thanks again.

Luke_C

Hi @SH_94

I'd suggest taking a stab at it yourself first. You could update that first condition with an OR statement to include your OP logic. Let me know if you have any issue doing that.

if [Document Type] = 'AC' then 0
elseif [Row+1:Document Type] = 'CZ' then [Amount]-[Row+1:Amount]
elseif [Row+1:Document Type] = 'OP' then [Amount]+[Row+1:Amount]
else null()
endif

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703