Amount reduction and addition with condition

[SH_94]

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

[Luke_C · Accepted solution]

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:

1. Why is the 4/28 row 0? The next row is OP not CZ so not sure why it's being subtracted.
2. 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. 

[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