56839_en-US

liliconcom

column formula based on variable number of selected rows

I am looking for an elgant column formula to replace a small 'for each row' statement.

 

I have a table with a value and a date (and other stuff).  The dates are irregularly spaced out.  I am looking for a formula for a rolling average of the value, sensitive to the date it was measured.  This sometimes includes just two values, but may include dozens.

 

The script below works, but it requires the script to be called.  I am hoping for a formula that can be stored in the column itself.

 

dt = current data table();
dt << new column ("LocalAverage", numeric);
 
for (ii=1, ii<=nrows(dt), ii++, 
         :LocalAverage[ii] = mean(:value[dt << getrowswhere(Abs(:date - :date[ii]) < 5*24*60*60)])
);

 

 (average over 5 days)

 

Re: column formula based on variable number of selected rowsI apologize. I was not aware it was possible to include multiple statements in a formula. This is eye opening. Thank you.Re: column formula based on variable number of selected rows

I must be missing something.......

The code I provided is a column formula, not an independent script.  

Re: column formula based on variable number of selected rowsTX, this is still a script. It will need to be run each time data is added to the table. I recognize that I failed to mention that earlier: The table needs to be up to date after new data is added. A formula column will accomplish that while your script and my script need to be called before the table is updated.Re: column formula based on variable number of selected rows

I believe the following formula will work.....or something close to it

ckDate = :date[row()];
mean(:value[dt << getrowswhere(Abs(:date - ckDate) < 5*24*60*60)]);