Identifying the first occurrence in a flag field

Question

Hi,

I need to identify the first occurrence of the value in Field D and then need to pull the value before the first occurrence from Field B and use it with other value from Field C.

from the above example,

1. I need to identify the first occurrence of value 1 in Field C

2. Pick the before value from Field B (i.e) 126

3. Need to subtract it with Field C (i.e) 192

4. Divide the value by Field A

e = (Field[c]-Field[B])/[Field A]

in this case, = 192-126/28 = 2.35

Please any idea o suggest how can i approach this?

Ronal_bal · Answer

With the same approach,

e output = (192-126)/100 = 0.66. And I need the output in the yellow colored line not in the previous as currently it is.

Sorry for sending the wrong requirement

JamesCharnley · Answer

@Ronal_bal the key to this is the multi-row tool to check if the next row is equal to the current in D. This works perfectly for your example but let me know if your real data has anything that might trip it up.

nextrowD.yxmd