Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Statistical functions in VFP6 (import)
Message
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Miscellaneous
Thread ID:
00435549
Message ID:
00436120
Views:
34
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:
***************************************************************************
*  Description.......: GetMedianValue - returns median value from a specified table
*  Calling Samples...: GetMedianValue('TranMstr','LstSlPrice','Price between 100000 and 130000')
*  Parameter List....: pcTableName, pcField, pcWhere
*  Created by........: Daniel Rouleau - original idea, Andrew Coates - Median definition
*  Modified by.......: Nadya Nosonovsky 10/30/2000 01:06:58 PM
*****************************************************************************
* Returns median value or .f. for unsuccessful cases
lparameters pcTableName, pcField, pcWhere

* pcTableName - name of the table or already opened cursor, which should be processed
* pcFiled     - name of the field, which used in calculation, price, for example
* pcWhere - where expression suitable for macro
* All parameters are optional, if they are not specified, current working alias is used and price field

** Check parameters first
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.
***************************************************************************
*  Description.......: GetMedianValue - returns median value from a specified table
*  Calling Samples...: GetMedianValue('TranMstr','LstSlPrice','Price between 100000 and 130000')
*  Parameter List....: pcTableName, pcField, pcWhere
*  Created by........: Daniel Rouleau - original idea, Andrew Coates - Median definition
*  Modified by.......: Nadya Nosonovsky 10/30/2000 01:06:58 PM
*****************************************************************************
* Returns median value or .f. for unsuccessful cases
lparameters pcTableName, pcField, pcWhere

* pcTableName - name of the table or already opened cursor, which should be processed
* pcFiled     - name of the field, which used in calculation, price, for example
* pcWhere - where expression suitable for macro
* All parameters are optional, if they are not specified, current working alias is used and price field

** Check parameters first
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
>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
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform