topic Re: Moving average and EWMA in Discussions

Moving average and EWMA

FR60 — Sat, 10 Jun 2023 23:29:31 GMT

Given a column with numerical values I would like to calculate the MA in this way:

MAi=1/N*SUM(j=i-1 to j=i-w) Xj

where w is the window width, i-1 the starting element and i-w the last one. Les't do an example with w=2

t x

-------------

1 x1

2 x2

3 x3 …..

4 x4 MA=(X3+X2)/2

5 x5 MA=(X4+X3)/2

6 x6 …..

7 x7

By using the New formula column-->Row feature

I set the Weighting to "equal". What about the Items Before and Items after to obtain what I reported before?

Same question if I want to use the Exponential weight.

Thanks Felice

Re: Moving average and EWMA

txnelson — Fri, 30 Apr 2021 13:06:16 GMT

Is the value of w equal to the number of columns in the data table, or is w equal to the number of the columns displayed on the screen, regardless of the number of columns in the data table?

Re: Moving average and EWMA

FR60 — Fri, 30 Apr 2021 15:28:40 GMT

Hi Jim w is not dependent on the number of column.

Thanks Felice

Re: Moving average and EWMA

MassimoMartucci — Mon, 03 May 2021 09:03:06 GMT

Hi,

if it is a simple moving average, you can easily compute it by using the Lag() function in Formula Editor.

Here below an example on how to calculate the moving average of previous 3 rows for Dissolution column in Tablet Production sample data table:

// Open Data Table: Tablet Production.jmp
// → Data Table( "Tablet Production" )
Open( "$SAMPLE_DATA/Tablet Production.jmp" );


// New column: Dissolution_MA3
Data Table( "Tablet Production" ) << New Column( "Dissolution_MA3",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
		(Lag( :Dissolution, 1 ) + Lag( :Dissolution, 2 ) + Lag( :Dissolution, 3 ))
		 / 3
	)
);


// Report snapshot: Tablet Production - Graph Builder
Data Table( "Tablet Production" ) << Graph Builder(
	Size( 799, 452 ),
	Show Control Panel( 0 ),
	Variables( Y( :Dissolution ), Y( :Dissolution_MA3, Position( 1 ) ) ),
	Elements( Line( Y( 1 ), Y( 2 ), Legend( 6 ), Row order( 1 ) ) )
);

Cheers, Massimo

Re: Moving average and EWMA

Mark_Bailey — Tue, 04 May 2021 15:31:48 GMT

See this function in the documentation and Scripting Index:

Re: Moving average and EWMA

ih — Wed, 05 May 2021 12:27:04 GMT

It looks like you want equal weighting with 1 row before and 0 after.

Re: Moving average and EWMA

FR60 — Wed, 05 May 2021 12:42:01 GMT

Let me do an example

Let's suppose to have the sequence 1 2 3 4 5 6 7 8 9

Starting from 5 I would like to do do the average of previous w rows. For w=3

(2+3+4)/3

and not

(3+4+5)/3

Rgds Felice

Re: Moving average and EWMA

ih — Wed, 05 May 2021 13:34:15 GMT

Ah, sorry I missed that detail. You can add a Lag formula around the Col Moving Average to accomplish this:

For your example:

New Table( "MA Example",
	Add Rows( 10 ),
	New Column( "Data",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] )
	),
	New Column( "MA",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Formula( Lag( Col Moving Average( :Data, 1, 2, 0 ) ) )
	)
)

Gives the expected result: