Sorry, used the same function twice. This is the corrected version of my previous post :)
>Yuri,
>
>I made research here and found, what my manager definition of Median was wrong. I consult with other managers as well and we decide to use the right Mathematical definition of Median rather than incorrect one. Therefore this is the function, I wrote:
>
lparameters pcTableName, pcField, pcWhere
if empty(pcTableName) or vartype(pcTableName)<>'C'
pcTableName=alias() && Current open alias
if empty(pcTableName) && No current table
return .f.
endif
endif
if empty(pcField) or vartype(pcField)<>'C'
pcField=upper('price')
else
pcField=upper(pcField)
endif
if empty(pcWhere) or vartype(pcWhere)<>'C'
pcWhere=''
else
pcWhere='where '+pcWhere
endif
local lnMiddleRecord, lcCursor, lnOldSelect, lnMedianValue
lnOldSelect=select() && Save current area
lcCursor='cur'+sys(2015) && Unique name
if used(lcCursor) && this should never happen
use in (lcCursor)
endif
select &pcField from (pcTableName) ;
&pcWhere ;
order by 1 ;
into cursor (lcCursor) nofilter
if _tally>3
lnMiddleRecord=(_tally/2) && Find middle record
else
=messagebox('Number of records is less than 3. Can not calculate median...',48)
if used(lcCursor)
use in (lcCursor)
endif
select (lnOldSelect) && Return to the original area
return .f.
endif
do case
case int(lnMiddleRecord)==lnMiddleRecord && Even number of records
local lnLowMedValue, lnHighMedValue
go lnMiddleRecord
lnLowMedValue = evaluate(lcCursor + '.' + pcField)
skip && Go to the next record in sequence
lnHighMedValue = evaluate(lcCursor + '.' + pcField)
lnMedianValue = round((lnLowMedValue+lnHighMedValue)/2,0) && Average of the two middle numbers
otherwise && Odd number of records
go int(lnMiddleRecord)+1
lnMedianValue=evaluate(lcCursor + '.' + pcField)
endcase
use in (lcCursor) && Close cursor
select (lnOldSelect) && Return to the original area
return lnMedianValue>This is another version of this program. It works much faster, if we have a necessary index.
lparameters pcTableName, pcField
local lcTableName, lnOldSelect, lnMedianValue, llCloseDBF, lnMiddleRecord, lcOrder
lnOldSelect=select() && Save current area
llCloseDBF=.f.
if empty(pcField) or vartype(pcField)<>'C'
pcField=upper('price')
else
pcField=upper(pcField)
endif
if empty(pcTableName) or vartype(pcTableName)<>'C'
pcTableName=alias() && Current open alias
if empty(pcTableName) && No current table
return .f.
endif
lcTableName=pcTableName && Alias
else
lcTableName=justfname(pcTableName) && Only Table Name without path
if not used(lcTableName) && The table was not used previously
local fh
fh=fopen(pcTableName,12)
if fh>0 and fclose(fh)>0 && File could be open exclusevely
use pcTableName again shared in 0 alias lcTableName
llCloseDBF=.t.
else
=messagebox(pcTableName +' is opened exclusively by another user. Can not proceed...',48)
return .f. && Table could not be opened (already used exclusively)
endif
endif
endif
select (lcTableName)
lnRecords=reccount() && Total number of records
if lnRecords<3
=messagebox('Number of records is less than 3. Can not calculate median...',48)
select (lnOldSelect) && Return to old area
return .f.
endif
lcOrder=tag() && Save current order
if tagno(pcField)=0 && Index on this field doesn't exist
if isexclusive(lcTableName) && Table is opened exclusively already
select (lcTableName)
index on &pcField tag (pcField)
else
fh=fopen(pcTableName,12)
if fh>0 and fclose(fh)>0 && File could be open exclusevely
use dbf(lcTableName) again exclusive in 0 alias WorkTable
select WorkTable
index on &pcField tag (pcField)
use in WorkTable
else
=messagebox(pcTableName +' is opened exclusively by another user. Can not proceed...',48)
return .f. && Table could not be opened (already used exclusively)
endif
endif
else
endif
select (lcTableName)
set order to (pcField) && Now it has this tag
go top
lnMiddleRecord=lnRecords/2
do case
case int(lnMiddleRecord)==lnMiddleRecord && Even number of records
local lnLowMedValue, lnHighMedValue
skip lnMiddleRecord-1
lnLowMedValue = evaluate(lcTableName + '.' + pcField)
skip && Go to the next record in sequence
lnHighMedValue = evaluate(lcTableName + '.' + pcField)
lnMedianValue = round((lnLowMedValue+lnHighMedValue)/2,0) && Average of the two middle numbers
otherwise && Odd number of records
skip int(lnMiddleRecord)
lnMedianValue=evaluate(lcCursor + '.' + pcField)
endcase
if llCloseDBF
use in (lcTableName)
else
set order to (lcOrder)
endif
select (lnOldSelect) && Return to old area
return lnMedianValue>>Nadja,
>>
>>Here is the definition :
>>*--------------------------
>>Median
>>When the elements of a set of numbers have been arranged in ascending order, the number located in the middle of the set is the median of the set. The median divides the set of data into two equal parts...
>>If the set contains an even number of elements, the median is the average of the two middle numbers.
>>*-----------------------------
>>And again, life does not make correction to math function. We have to choose the appropriate math function. For example, when you consider the driving strategy: half of the driving TIME with the speed V1, another half of the driving TIME with another speed V2, then the average speed is the arephmetic average. But if the driving strategy is : half of the DISTANCE with the speed V1, another half of the DISTANCE with another speed V2, then the average speed will not equal to the arithmetic average.
>>
>>
>>>Thanks for the reply, Yuri. Could you please check this definition for me in >those books, because I simply don't have them. I agree, that life may make >some corrections in math functions :)
>>
>>>Besides, Andrew quoted the book of Theory of Probabilities... It's not the >same, as Financial Statistics, I believe...
If it's not broken, fix it until it is.
My Blog 
