I'm trying to find the median value of an array that contains some null values but alas I've been unsuccessful and haven't found anything similar in the forums that might help me.
Bit of background - I'm trying to create statistics for various instruments over various time periods and one of them is the Median (and later I hope to use Percentiles as well). To illustrate what I'm trying to achieve I have the following code ...
Version( 6.30 ); barsInRange = LastValue( Cum( Status( "barinrange" ) ) ); lbir = Status( "lastbarinrange" ); medianHighValue = ValueWhen( lbir, Median( High, barsInRange ) ); higherHigh = High > Ref( High, -1 ); higherHighValue = IIf( higherHigh, High, Null ); medianHigherHighValue = ValueWhen( lbir, Median( higherHighValue, barsInRange ) ); percentileHigherHighValue = ValueWhen( lbir, Percentile( higherHighValue, barsInRange, 50 ) ); Filter = 1; AddColumn( High, "High", 1.5 ); AddColumn( medianHighValue, "Median High Value", 1.5 ); AddColumn( higherHigh, "High > Ref( High, -1 )", 1.0 ); AddColumn( higherHighValue, "Higher High Value", 1.5 ); AddColumn( medianHigherHighValue, "Median Higher High Value", 1.5 ); AddColumn( percentileHigherHighValue, "Percentile Higher High Value", 1.5 );
I've run this through an exploration and get the following output ...
The median for the High Value array is correct by my calculations but the median for the Higher High Value array is incorrect. By my calculations it should be 1.06199 but the AFL is producing 1.06002. I'm pretty sure it's because of the null values in the Higher High Value array but I don't know how to make it work.
I've tried using SparseCompress but haven't had any luck and I've spent a good 6 or so hours trying to work out a solution. Can anyone give me a hint as to what path I should follow?