topic Re: Moving average and Weighted moving average on a data set in Discussions

Moving average and Weighted moving average on a data set

anandk — Mon, 20 Mar 2017 19:59:38 GMT

Hi,

I am trying to do a moving average and a weighted moving average on the data set that I have posted below.

I want to do a moving average to the 'Mean' column.

Also can I repeat this formula by the 'Sample' group in my data set

Re: Moving average and Weighted moving average on a data set

txnelson — Mon, 20 Mar 2017 20:22:37 GMT

Here is a moving average formula

Mean( Lag( :Points, 1 ),
	 Lag( :Points, 2 ), 
	 Lag( :Points, 3 ), 
	 Lag( :Points, 4 ), 
	 Lag( :Points, 5 ) 
 )

and a weighted moving average

Mean(
	Lag( :points, 1 ) * :mean,
	Lag( :points, 2 ) * :mean,
	Lag( :points, 3 ) * :mean,
	Lag( :points, 4 ) * :mean,
	Lag( :points, 5 ) * :mean
)

Re: Moving average and Weighted moving average on a data set

anandk — Tue, 21 Mar 2017 01:12:14 GMT

Hi txnelson,

Is the below formula you suggested moving average for the 'Points' column?And you are using the Mean() formula?

How can I put this in a for loop to do it over all the rows?

Also I want to do this By Group

Re: Moving average and Weighted moving average on a data set

txnelson — Tue, 21 Mar 2017 01:39:01 GMT

Using a formula to define the values of a column is an essential part of JMP. It is well documented in the JMP book called "Using JMP". I suggest you read that book. It was installed on your computer when you installed JMP. You get to it under the Help pulldown menu

Help==>Books==>Using JMP

Regardless, here is how to use the formula that I provided you, create a new column in your data table and then right click on the column header and select "Formula"

As you can see in my example, I named the new column "Moving Average"

The next window that will open up, is the Formula Editor

It allows you to point and click and create whatever formulas you want. However, you can also double click in the blue outlined box that has the wording "no formula" and the editor will come up

As seen in the above window, I just typed in the formula, or it can be pasted into the window. Then when you click on the "OK" button, you will see the graphical form of the formula

Then when you click on the "OK" button, or the "Apply" button the formula will be run for the new column

Re: Moving average and Weighted moving average on a data set

anandk — Tue, 21 Mar 2017 01:44:17 GMT

I was able to get the 3 point Moving Average using.I was able to apply the formula as well.

"If( Row() < 3,
Mean( :Mean1[Index( 1, Row() )] ),
Mean( :Mean1[Index( Row() - (3 - 1), Row() )] )
)"

Ho can I do this By Group for the Column "Sample"?

For each value of Sample I want a moving average for the "Mean1" values and I have 239 Sample values.

Thank you for your help

Re: Moving average and Weighted moving average on a data set

txnelson — Tue, 21 Mar 2017 02:14:28 GMT

It's just a simple extension of your formula. You just have to keep track of when the :Sample changes, and then start the calculations from scratch

If( :sample != Lag( :sample, 1 ) | Row() == 1,
	counter = 0;
	startrow = Row();
);
counter = counter + 1;
If( counter < 3,
	Mean( :Mean1[Index( startrow, Row() )] ),
	Mean( :Mean1[Index( Row() - (3 - 1), Row() )] )
);

Re: Moving average and Weighted moving average on a data set

anandk — Tue, 21 Mar 2017 03:24:32 GMT

Thank you for your prompt replies.It worked for my data set

Re: Moving average and Weighted moving average on a data set

anandk — Tue, 21 Mar 2017 20:36:59 GMT

How can I do a weighted moving average with the above formula with the coefficients 1,4,6,4,1 ?

Re: Moving average and Weighted moving average on a data set

txnelson — Tue, 21 Mar 2017 21:37:25 GMT

See the note at the bottom of this reply

~~The formula that is using the index() function isn't going to work for a weighted calculation. What I assume you want to do is illustrated by the following formula~~

Mean(
	Lag( ::Mean1, 1 ) *  1,
	Lag( ::Mean1, 1 ) *  4,
	Lag( ::Mean1, 1 ) *  6,
	Lag( ::Mean1, 1 ) *  4,
	Lag( ::Mean1, 1 ) *  1
);

~~This structure doesn't allow the simple 2 level calculation, so it has to be expanded, but is is basically the same logic~~

If( :sample != Lag( :sample, 1 ) | Row() == 1,
	counter = 0;
);
counter = counter + 1;
If( counter == 1,
	:Mean1,
	counter ==2,
		Mean(
			Lag( ::Mean1, 1 ) *  1,
			Lag( ::Mean1, 1 ) *  4
		),
	counter ==3,
		Mean(
			Lag( ::Mean1, 1 ) *  1,
			Lag( ::Mean1, 1 ) *  4,
			Lag( ::Mean1, 1 ) *  6
		),
	counter ==4,
		Mean(
			Lag( ::Mean1, 1 ) *  1,
			Lag( ::Mean1, 1 ) *  4,
			Lag( ::Mean1, 1 ) *  6,
			Lag( ::Mean1, 1 ) *  4
		),
	Mean(
		Lag( ::Mean1, 1 ) *  1,
		Lag( ::Mean1, 1 ) *  4,
		Lag( ::Mean1, 1 ) *  6,
		Lag( ::Mean1, 1 ) *  4,
		Lag( ::Mean1, 1 ) *  1
	)
);

And after all of this, I found the Col Moving Average() function. So don't do anything above that I suggested, instead, go to

Help==>Scripting Index==>Col Moving Average

and you will get a single function that will do everything you have asked for

Re: Moving average and Weighted moving average on a data set

anandk — Tue, 21 Mar 2017 21:48:38 GMT

Will this formula divide the weighted mean by the weights sum that is 16?

I am not getting the expected answer using the formula

Re: Moving average and Weighted moving average on a data set

anandk — Tue, 21 Mar 2017 21:56:01 GMT

I am not seeing the Col Moving Average function in JMP 12

Re: Moving average and Weighted moving average on a data set

anandk — Tue, 21 Mar 2017 22:00:13 GMT

I dont see the Col Moving Average function in JMP 12

Re: Moving average and Weighted moving average on a data set

txnelson — Wed, 22 Mar 2017 03:09:39 GMT

The Col Moving Average() function was added in JMP 13. So, for JMP 12 you will need to used the code I provided. And all you need to do, is to add the division by 16 to the code. Make sure you divide the 1,2,3,4 lag subsets with the appropriate subset the the 16

Re: Moving average and Weighted moving average on a data set

anandk — Wed, 22 Mar 2017 15:01:20 GMT

If( :sample != Lag( :sample, 1 ) | Row() == 1,
counter = 0
);
counter = counter + 1;
If(
counter == 1, :Mean1,
counter == 2, Mean( Lag( ::Mean1, 1 ) * 1, Lag( ::Mean1, 1 ) * 4 ) / 16,
counter == 3,
Mean( Lag( ::Mean1, 1 ) * 1, Lag( ::Mean1, 1 ) * 4, Lag( ::Mean1, 1 ) * 6 )
/ 16,
counter == 4,
Mean(
Lag( ::Mean1, 1 ) * 1,
Lag( ::Mean1, 1 ) * 4,
Lag( ::Mean1, 1 ) * 6,
Lag( ::Mean1, 1 ) * 4
) / 16,
Mean(
Lag( ::Mean1, 1 ) * 1,
Lag( ::Mean1, 1 ) * 4,
Lag( ::Mean1, 1 ) * 6,
Lag( ::Mean1, 1 ) * 4,
Lag( ::Mean1, 1 ) * 1
) / 16
);

Will the formula look like this or I have to divide the Lag of weights 1 and 4 by 5 and so on?

Re: Moving average and Weighted moving average on a data set

txnelson — Wed, 22 Mar 2017 15:27:45 GMT

For the cases where "Counter" is less than 5, you need to adjust the divisor down to the sum of the weights for each of those levels, so the code should look like:

If( :sample != Lag( :sample, 1 ) | Row() == 1,
	counter = 0
);
counter = counter + 1;
If(
	counter == 1, :Mean1,
	counter == 2, Mean( Lag( ::Mean1, 1 ) * 1, Lag( ::Mean1, 1 ) * 4 ) / 5,
	counter == 3, Mean( Lag( ::Mean1, 1 ) * 1, Lag( ::Mean1, 1 ) * 4, Lag( ::Mean1, 1 ) * 6 ) / 11,
	counter == 4,
		Mean( Lag( ::Mean1, 1 ) * 1, Lag( ::Mean1, 1 ) * 4, Lag( ::Mean1, 1 ) * 6, Lag( ::Mean1, 1 ) * 4 ) /
		15,
	Mean(
		Lag( ::Mean1, 1 ) * 1,
		Lag( ::Mean1, 1 ) * 4,
		Lag( ::Mean1, 1 ) * 6,
		Lag( ::Mean1, 1 ) * 4,
		Lag( ::Mean1, 1 ) * 1
	) / 16
);