Row Math - Changing formula after first row

KNIME Analytics Platform
1

So, I am trying to calculate 14 day Average True Range (ATR) for currency data.  The calculation involves the row 1 to be calculated taking a 14 period  simple average of True Range (a different column).  But starting row 2 the previous row of ATR is referenced to calculate ATR

Row ID True Range (TR) Average True Range (ATR)
1 1.39 =1.411 (AVG(TR1-14)
2 .9 =1.375 (ATR1 x (14-1)+TR2/14
3 1.5 =1.384
4 0.7 =1.335

How can Knime handle this where first row has a different formula (refrences only adjacent column), while second row onwards there is a different formula that references the previous row.  Appreciate your help

2

You can calculate this with the Moving Aggregation node.

Best Iris

3

Hi Iris,

Thanks for the reply.  I don't think moving aggregation does that.  One cannot calculate the first row with different formula and starting the second row use a different formula in moving aggregation.  Moving aggregation will give me the first row, true, but how do I get the second row.  That is where I am stuck.  The second row on there is a reference to the previous row value.  

4

The formula in bracket in the ATR column is what I am showing how the value in teh ATR column was calculated

5

The Math node does not have access to the results of previous rows. The only think I can think of right now is using a Java Snippet. There you have all the flexibiltiy you need and you can save previous row values in member variables.

Just to have it mentioned: The lag column node can create you a "previous cell" column. But it would still not be enought for your case, I think.

6

Much appreciate your feedback in this.  I suspect that it will be done in Java as well.  The lag column doesnot do the trick either.  As you cannot have two different formulas in the same column, meaning first row referring lag column and second row referring row 1.  

Unfortunate for me, I do not know how to code in java, also I think this exercise may require loops as well.  I am attaching an excel sheet in here that shows the ATR calculation.  I really wish Knime had nodes for financial technical analysis.

Maybe someone has done the ATR calculation already and can post a referencing workflow here.

7

I am sorry I had attached the wrong ATR file in the previous post.  PLease find the proper ATR calculation file.  I have tried this with Java snippt as well.  I can't seem to make this work.  Can anyone offer a java code help for this.  Much appreciated.

 

Thanks,

 

8

For me I am finding this tough even in java as the first valye of the column is a simple average and the rest of the rows need a different calculation.

9

I think you are right, the fastest way to calculate this in using a Java Snippet. See the screenshot of the dialog attached.

 

 

10

Yes Iris,  Very smart,that is the trick, using row index.  That way a different formula can be specified for different rows in java. Thank you so much for looking in to this.  I will post my calculation soon enough as well.  What a vibrant community of supporters we have at Knime.  This is great!

1 Like