trying to write a script to compare future predicted values

[#1]

Hi all - I am still learning the basics of JSL and am trying to write a script that will compare how well the "point in time" predicted value compares to subsequent predicted values over the following 7 days.  I am trying to get the script to do the following:

1 - perform regression on all rows in data table (each row corresponds to a 15 minute increment, so my predicted value is computed every 15 minutes).

Ex:  

obj = Fit Model(
    Y( :LOG_ECOLI_Pear ),
    Effects( :LOG_TURB_Pear, :millsR_Q_lag6hr ),
    Personality( "Standard Least Squares" ),
    Emphasis( "Effect Leverage" ),
    Run( :LOG_ECOLI_Pear )
);
obj << Prediction Formula;

2 - select the predicted value that occurs each Monday morning at 8 am throughout the dataset spanning 3 years.

3 - compare that value to all the other predicted values that occur during the week following that 8 am value (i.e. Mon 8:15 am to the following Mon at 7:45 am).  (That is, all the values computed during the week will be compared to the Monday at 8 am value.)  Then, count the number of rows in which all those predicted values are more than + or - 25% of the 8 am value for that week.  Continue (repeat this process) until all the weeks are accounted for. 

ex:   

New Column( "count num above or below 25pct of mon 8am",
    numeric,
    continuous,
    formula(
        If( Row() == 1, x = 0 );
        ((If( :above or below 25pct of mon 8am == 1,
            x = x + :above or below 25pct of mon 8am,
            X = X + :above or below 25pct of mon 8am
        )));
        x;
    )
);

I'm not sure how to select the week's worth of values to compare to the Mon 8 am value.  And I'm not sure I'm even on the right track with this.  I would greatly appreciate any help!

[#2]

If it helps......

My data table is set up as follows:

datetime                    log_turb_pear       millsR_Q_lag_6hrs    pred formula log_ecoli_pear (computed)

01/04/2021 08:00  AM        1.32                      50                                  2.64

01/04/2021 08:15  AM        1.46                      45                                  2.72

01/04/2021 08:30 AM         1.59                      50                                  2.90

01/04/2021 08:45 AM         2.20                      50                                  3.7

01/04/2021 09:00 AM         2.25                      58                                  3.19

etc

I am trying to compare the value of :pred formula log_ecoli_pred at 1/4/2021, 8 am (i.e. 2.64) to the value obtained at all the other times up to 01/12/21 at 7:45 AM.  And then I need to count the number of times that each of those values is + or - 25% of 2.64.  Then repeat for the next week, etc until the counting has been carried out for all weeks in the data table.  (Note that, if needed, the first Monday in 2019 is on Jan 7th, the first Monday in 2020 is Jan 6, and the first Monday in 2021 is Jan 4....my data table has records from early 2019 to current.)    Only one value (3.7) in the above example table is more than + or - 25% of 2.64 (i.e. <(0.75 * 2.64) or > (1.25 * 2.64)). 

[#3]

For simplicity's sake, my question really boils down to this:   how would I retrieve/read a column value from the upcoming row (I'll call it row n+1), do a computation with it, then retrieve from the row after that (n+2) and do the computation, and so on until I reach row n=672 (which is 7 days worth of 15 minute increments)......then start over doing a new computation beginning at row n=672 and continue the same looping workflow for the next 672 rows.....and then start over once again at n = 672 +672 = 1344......and so on until I run out of rows in the data table.   I'm happy to clarify if needed.  Even a hint might get me on track.  Thanks in advance!  

[#4 · Accepted solution]

Hi @learning_JSL ,

Here are some ideas to get you started; code not tested.

//Set Initial Constraints
strt_row = 1;
end_row = 672;
ref_col_name = "pred formula log_ecoli_pear";
dt = Data Table( "community_post.jmp" );
cnt_ref = 0;
cnt_ref_2 = 0;
//Create Loop
for(r = 1, r <= NRows(dt), r++,
	//find begin date
	mod_strt_row = Modulo( dt:datetime[r], dt:datetime[1] );
	If(mod_strt_row == 0, strt_row = row() );
	//find end date 
	mod_end_row = Modulo( row(), end_row );
	If(mod_end_row == 0, end_row = row() );
	If(r == strt_row,
	//get reference times and value for start row and column
		ref_strt_time_value = dt:datetime[strt_row]; 
		ref_col_name_value = Column(dt, ref_col_name)[strt_row];
		show( format(ref_strt_time_value, "m/d/y h:m", 19), ref_col_name_value )
	,
		r!=end_row & r > 1,
		row() = r;
		compare_value = Lag( Column(dt,ref_col_name)[], 1);
		low_limit_ref_value = 0.75*ref_col_name_value;
		upper_limit_ref_value = 1.25*ref_col_name_value;
		calc_val = Abs( ( ( ref_col_name_value - compare_value ) / ref_col_name_value ) * 100);
		If(ref_col_name_value >= low_limit_ref_value | ref_col_name_value <= upper_limit_ref_value,
			cnt_ref_2 = cnt_ref_2 + 1;
			show( calc_val, cnt_ref_2);
		
		);
		If( calc_val <= 25, 
			cnt_ref = cnt_ref+1;
			show(calc_val, cnt_ref);
		);
	,
		r == end_row,
		ref_end_time_value = dt:datetime[end_row];
		show(format(ref_end_time_value, "m/d/y h:m", 19) );
	);
);

cnt_ref_2;
cnt_ref;

[#5]

Thank you Stan.  I will give this a try.

[#6]

Wow.  Thanks Stan! 

I have poured over this for an hour and have to concede that, unfortunately, it is above my current level of JSL understanding.  When I run it it produces a table showing datetime, log_turb_pear, millsR_Q_lag_6hrs, and pred formula log_ecoli_pear columns but nothing else is shown.  I'm sure it's user error (on me!) but I'm not sure how to debug it as I am too new to scripting.  I know you put a lot of effort into this and I very much appreciate it.  I'll keep at it - thank you.