topic Re: Rolling average (30 days) using continuous time data in Discussions

Rolling average (30 days) using continuous time data

ControlKangaroo — Sun, 11 Jun 2023 11:25:10 GMT

I have 180 days of recent data totaling 2574 rows in JMP. I have included a file with just two columns, one is the DATETIME stamp and the other is the MAX_VALUE data at that timestamp.

I have created an X-Y chart of MAX_VALUE (Y-Axis) vs DATETIME (X-Axis).

I wish to add a trendline where its value at any given time is equal to the average value of MAX_VALUE over the past 30 days.

Can somebody help me fit a line for the 30day moving average of this dataset?

thanks.

Re: Rolling average (30 days) using continuous time data

HadleyMyers — Wed, 22 Jun 2022 11:58:08 GMT

Hi,

You can add a formula column using the Col Moving Average function:

Col Moving Average( :MAX_VALUE, 1, 29, 0 )

Re: Rolling average (30 days) using continuous time data

ControlKangaroo — Wed, 22 Jun 2022 12:38:56 GMT

Unfortunately, this only averages the previous 30 values of MAX_VALUE. My data is not in discrete increments, for example, the previous 30 days for one output should have about 350 inputs, whereas the Col Moving average suggestion you have given me would have just 30 inputs (the previous 30 datapoints). Can the input for ARG 2 (Weighting), ARG 3 (Before), ARG 4 (After) etc be manipulated to use DATETIME as an input such that it applies to every other row where DATETIME is less than or equal to the current rows DATETIME value?

Re: Rolling average (30 days) using continuous time data

jthi — Wed, 22 Jun 2022 13:18:58 GMT

You could create DATE column to your data,then create summary table by using that date and the data column. Then calculate the rolling average (you might have to add missing dates, depending on how you want to manage them). After this has been calculated you can join it back to the original data.

Re: Rolling average (30 days) using continuous time data

Byron_JMP — Wed, 22 Jun 2022 13:43:46 GMT

Not 100% sure this is right, but its close.

Includes a formula to identify each individual day.

Then uses an abuse of the moving range function in a other column.

Re: Rolling average (30 days) using continuous time data

ControlKangaroo — Wed, 22 Jun 2022 15:23:47 GMT

This is actually correct, I had arrived at this same dataset by using the following function:

Col Moving Average( :MAX_VALUE, 1, :DATETIME <= 30 * 86400, 0 )

But this function increments by seconds for 1 month (Over 2.5 million iterations) and JMP struggles for a minute before outputting the result.

The way you have done it is much less memory demanding, Thanks!