381181_en-US

liliconcom

Moving average and EWMA

Hi

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

 

Mass CustomizationRe: Moving average and EWMA

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:

 

 

Re: Moving average and EWMA

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

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

Re: Moving average and EWMA

See this function in the documentation and Scripting Index:

 

Re: Moving average and EWMA

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

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

 

Thanks  Felice

Re: Moving average and EWMAIs 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?