Forum Discussion
Reset next row based on compounded total
Done (I didn't create [grouping] column because it was not necessary for this purpose).
Hello dufoq3 ,
I have now added a table for expected output.
I have highlighted the cells that are different compared to the original table.
Hope this makes sense.
Hi, I've create same as you but in a simple query (file is attached).
You can find 2 new versions (so you can delete all the others).
Check [group comp return] column please, because in my opinion you want my v2, but let me know please.
My question is:
why ther is 0 here when signal is 1?
Hello dufoq3
In your v1 the returns are being compounded which is correct.
In your v2 they are being summed.
Although the results are similar in a small group of values I do need the returns to be compounded as in v1.
I would like the position (current row) to be conditional on BOTH the signal (current row) AND the group comp return (GCR) (previous row).
So, even though the signal is 1 in row 20, because the GCR (row 19) had reached -2.92% (i.e. <= -1%) the position in row 20 should reset to 0.
Return (row 20) would then be 0% (0.70%*0) , grouping (row 20) would calcuate to 0 and the GCR (row20) would thus reset to 0%.
The position would then stay at 0 until the next signal of 1 in row 24 and the returns then compounded until either a signal of -1 or GCR <= -1%.
I basically got stuck because the calculated return, grouping and GCR (row 19) are derived from the position (row 19) and I couldn't figure out how to use the GCR calculated in that previous row (19) in the calculation of the new position in the current row (20).