Choose Language Hide Translation Bar
Highlighted
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
Highlighted
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
Highlighted
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

Highlighted
Feli
Level III

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?

Highlighted
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

Highlighted
Feli
Level III

Re: Calculate a moving median

That helped me, indeed!
Article Labels

    There are no labels assigned to this post.