cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
RVhydrA
Level III

Calculate a moving median

I am trying to calculate a moving median. I have used the moving average function before as well as the Summation function to calculate moving averages but can't figure out how to calculate a moving median based on a set number of lag rows. For example, for every row in my column, i'd like to query the previous 650 rows and calculate the median. I'm working in the GUI right now so any thoughts on how to do this in the column formula?

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Calculate a moving median

Here is a formula that works for a new column added to the Big Class data table that calculates the moving median for the last 5 rows.  I believe you can adapt it to your needs

to = Row();
If( to - 5 < 0, t	from = 1,
	from = to - 5
);
Quantile( 0.5, :weight[Index( from, to )] );
Jim

View solution in original post

4 REPLIES 4
txnelson
Super User

Re: Calculate a moving median

Here is a formula that works for a new column added to the Big Class data table that calculates the moving median for the last 5 rows.  I believe you can adapt it to your needs

to = Row();
If( to - 5 < 0, t	from = 1,
	from = to - 5
);
Quantile( 0.5, :weight[Index( from, to )] );
Jim
Feli
Level IV

Re: Calculate a moving median

I have trouble making this work for my example.

Would I copy this into the formula editor or in a JSL script?

I tried pasting into the formula editor (JMP 14) but it does not recognize the "from" in 

:weight[Index( from, to )]

 @martindemel Any hints?

RVhydrA
Level III

Re: Calculate a moving median

paste this entire script into the formula editor

 

 

to = Row();
If( to - 5 < 0,
	t from = 1,
	from = to - 5
);
Quantile( 0.5, :Column[Index( From, To )] );

 

 

:Column and From need to be changed. So column is obviously the column you want to calculate the median, From is the number of rows you want to look backwards. so if I want the median between the current row and the previous 10, i'd type -10 for From. Hope that helps

Feli
Level IV

Re: Calculate a moving median

That helped me, indeed!