I would like a built-in function similar to Col Moving Average() that computes a moving (rolling) quantile of a a column. You could call it Col Moving Quantile() and/or Col Moving Median().
I wrote a user function (attached) which works well enough for small tables, but it uses a very inefficient approach. I know for absolutely sure that there are much more efficient ways to implement this function that don't involve brute force recalculation at each rolling step. Those approaches are beyond my coding ability.
Add Custom Functions(
New Custom Function(
"x",
"Col Moving Quantile",
Function( {col, p, before, after, dt = Current Data Table()},
{coldata = Column( dt, col ) << Get As Matrix, from = Max( Row() - before, 1 ), to, to = Min( Row() + after, N Rows( dt ) )},
Quantile( p, coldata[Index( from, to )] )
)
)
// === Documentation ===
<<Formula Category( "Statistical" ) //
<<Scripting Index Category( "Statistical+" ) //
<<Description( "Returns the moving (rolling) quantile of the specified column." ) //
<<Prototype( "x:Col Moving Quantile( ColName( string ), p, before, after, <Table reference> )" ) //
<<Parameter( "String", "ColName( string )" ) //
<<Parameter( "Number", "p" ) //
<<Parameter( "Number", "Before" ) //
<<Parameter( "Number", "After" ) //
<<Parameter( "Name", "Table" )
);