Median Value of Array with Null Values

AFL Programming
1

Hi

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 ...

image
image1188×494 24.1 KB

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?

Many thanks

Craig

2

Don't use NULLS in the middle of otherwise non-null array.

Nulls are meant to appear only AT THE BEGINNING of the array.

Your code should be changed. Instead of:

higherHigh = High > Ref( High, -1 );
higherHighValue = IIf( higherHigh, High, Null ); // BAD IDEA

Use dedicated function that returns "higher high".

higherHighValue = Highest( High );
1 Like
3

"Don't use NULLS in the middle of otherwise non-null array." Got it. Won't do that again.

That's led me back down the SpareCompress path and although my code may not be terribly elegant it's giving me the median and percentile values that I was expecting ...

Version( 6.30 );

higherHigh = High > Ref( High, -1 );

only_when = higherHigh AND Status( "barinrange" );
higherHighCount = LastValue( Cum( only_when ) );

x = SparseCompress( only_when, High );
medianHigherHighValue = Median( x, higherHighCount );
percentileHigherHighValue = Percentile( x, higherHighCount, 50 );

Filter = 1;
AddColumn( High, "High", 1.5 );
AddColumn( higherHigh, "High > Ref( High, -1 )", 1.0 );
AddColumn( x, "Higher High Value (Compressed)", 1.5 );
AddColumn( medianHigherHighValue, "Median Higher High Value", 1.5 );
AddColumn( percentileHigherHighValue, "Percentile Higher High Value", 1.5 );

image
image1157×474 27.8 KB

Thanks for the help.

4

If you are using SparseCompress you should call SparseExpand. Otherwise timestamps are wrong.
Read the comments in TimeFrame docs that have sibling Compress/Expand functions
https://www.amibroker.com/guide/h_timeframe.html