Find the median age of a group of people

[Ronald Anusiewicz]

I�m running a statistical report about members of an organization. I have the formula below for finding the average age of members at a given time. This formula works fine. What I�m trying to figure out is how to go about finding the median age for these members.

The formula below gives me the average of the table, persons, filtered to find the active members whose birthdate is not blank and the average age as of a user supplied date. That date is stored in the variable vDate_Report_End.

tableavg("persons","p_status=\"A\".and.remspecial(dtoc(birthdate))<>\"\" ","age(birthdate,Var->vDate_Report_End)")

There doesn�t seem to be much at all in the message board. What little I could find, I�m not sure how I could apply it to a report. The table currently has less than 500 recorders but could possible have up to 5000 records.

Ron

[Stan Mathews]

Re: Find the median age of a group of people

See if this works. I gave it a minimal test.

FUNCTION tst_mean AS N (tablename AS C)
	tbl = table.open(tablename)
	recs = tbl.records_get()
	tbl.order("birthdate")
	tbl.fetch_first()
	if mod(recs,2) = 1 'odd count
		for qx = 1 to int(recs/2)
			tbl.fetch_next()
		next
		tst_mean = age(tbl.birthdate,date())
		tbl.close()
	else
		for qx = 1 to int(recs/2)-1
			tbl.fetch_next()
		next
		lower_val = age(tbl.birthdate,date())
		tbl.fetch_next()
		upper_val = age(tbl.birthdate,date())
		tbl.close()
		combined = lower_val+upper_val
		tst_mean = combined/2
	end if
END FUNCTION

[Ronald Anusiewicz]

Re: Find the median age of a group of people

Stan,

Thank you very much.

I created three layout variables. I then put your modified function into the reports OnPrintInit event. It appears to be working "OK".
I did put the same data into an excel spreadsheet which did yield slightly different numbers. But then the excel numbers were slightly different from the built in alpha function tableavg.

alpha avg 43.7 mean 40.5
excel avg 44.2 mean 41.1

The test data was only 20 records. I'm not should if rounding could be making the difference.

var->vnActMean = tst_Mean("persons", "A", vDate_Report_Start, vDate_Report_End )	'Active
var->vnInActMean = tst_Mean("persons", "I", vDate_Report_Start, vDate_Report_End )	'Inactive
var->vnLeftMean = tst_Mean("persons", "L", vDate_Report_Start, vDate_Report_End)	'Left

FUNCTION tst_mean AS N (tablename AS C, Status as C, sDate as D, Date as D )
	IF Status = "L" THEN
		filter = "P_Status="+s_quote(status)+".and.remspecial(dtoc(birthdate))<>\"\".and."+between_date("D_left",sDate,Date)
	ELSE
		filter = "P_Status="+s_quote(status)+".and.remspecial(dtoc(birthdate))<>\"\" "
	END IF
	
	tbl = table.open(tablename)
	query.description = "Mean"
	query.order = "birthdate"
	query.filter = filter
	qry = tbl.query_create()
	recs = qry.records_get()
	i = tbl.index_primary_put("Mean")
	tbl.fetch_first()
	IF mod(recs,2) = 1 'odd count
		FOR qx = 1 TO int(recs/2)
			tbl.fetch_next()
		next
		tst_mean = age(tbl.birthdate,date)
		qry.drop()
		tbl.close()
	ELSE
		FOR qx = 1 TO int(recs/2)-1
			tbl.fetch_next()
		next
		lower_val = age(tbl.birthdate,date)
		tbl.fetch_next()
		upper_val = age(tbl.birthdate,date)
		qry.drop()
		tbl.close()
		combined = lower_val+upper_val
		tst_mean = combined/2
	END IF
	
END FUNCTION

Again,

Thank you very much.

Ron

[Tom Cone Jr]

Re: Find the median age of a group of people

Ron,

Pls supply copy of the sample data. Thanks.

-- tom

ps. I'm curious why your function reference the mean ? Thought you were looking for the median. The mean <> the median.

[G Gabriel]

Re: Find the median age of a group of people

I did put the same data into an excel spreadsheet which did yield slightly different numbers. But then the excel numbers were slightly different from the built in alpha function tableavg.

alpha avg 43.7 mean 40.5
excel avg 44.2 mean 41.1

Ron:
Are you looking for "Mean"? or "Median"?
The title to your thread refers to Median, but this post talks about "Mean".
Mean and Median are completely two different animals.

[Ronald Anusiewicz]

Re: Find the median age of a group of people

Well, I guess my ignorance of statistics is showing.

Yes, I was looking for the Median age, not the mean.
This is just part of a bigger picture where I'm looking for the youngest, oldest, average and median age of members during a given time period.

For the year 2011, the youngest, oldest, etc....

Because I'm using real data with personnel information, it would take me some time to put together a sample workspace, but for now I can give you the excel worksheet.

Ron

[Ronald Anusiewicz]

Re: Find the median age of a group of people

This was a little easier than I thought it would be.

Here's a workspace example with my real data with private information removed.

I'm having other issues with the report, but for now, I'm just trying to work on the average and median ages.

Thanks,

Ron

[G Gabriel]

Re: Find the median age of a group of people

So you want the median.
The Median is the record right smack in the middle.
If you have an odd number of records, say 11 records, and if you sort them according to the field in question, then the median is the value of the field in record #6 (not recno, but #6 according to the sort order).
If you have an even number of records, say 12, then the median is the average value of the two records in the middle, i.e. #6 & #7. The 6th record is preceded by 5 records and the 7th record is followed by 5 records.
That's how you arrive at the median.
The question becomes, how do you get to that record (or two records) in the middle of a bunch of records?
You could do that by many means, but ...what is the shortest cut?
Well, here is one way:

The middle record is the top record of the bottom half (or the bottom record of the top half). And when I say half, its half plus one if the total number is odd or the bottom record of the top half and the top record of the bottom half if the total number of records is an even number. Hope you are still with me!

With that concept in mind, you could write a relatively lengthy expression or design an UDF to do that.

So, here are your working blocks:
top_records(1,bottom_records(Num_of_records/2(..with consideration for odd and even..), filter))

There is a nifty way to calculate this number (in red)..
Let me see if I could put this in an UDF for you..but I am pretty sure you could do it yourself..
You could also make use of TOP_RECORDS_LIST()

[Ray in Capetown]

Re: Find the median age of a group of people

My great great grandpa used to say (not to me of course, I wasnt around then)
"You can make a programmer out of a mathematician
But you cant take the mathematician out of that programmer."

(Well he must have meant an abacus programmer)

[Tom Cone Jr]

Re: Find the median age of a group of people

Here's my contribution.

I extracted 14 records from the main table and used the smaller table to validate my results.

Unzip and extract the files within the attachment to an empty folder.

Run Toms_Script to see results in trace window.

-- tom

[G Gabriel]

Re: Find the median age of a group of people

Here is another short cut:
let's say you have 100,001 that is one hundred thousand and one records that meet the filter..
And you want to jump to the record right smack in the middle.
What is the shortest cut?
Easy:
(For convenience and expediency I am illustrating here based on odd number of records, you could modify this to deal with even numbers)
First find the rank of the middle record:
x=round_up(100001/2,0) 'this=50001
So now we want to jump to this record. How?
t=table.open(...)
t.fetch_first()
for i=1 to x step x 'you probably never thought you could replace the step with a variable. I didn't either, but it works.
t.fetch_next()
next
That will get you straight to your desired record, the one in the middle, in one measly step!.
Have fun.

[G Gabriel]

Re: Find the median age of a group of people

Here is an untested function. Please test first:

FUNCTION Median AS N (tablename AS C, fieldname as C, filter as C )
	t=table.open(tablename)
	t.query_create("t",filter,fieldname)
	cnt=t.records_get()
	x=round_up(cnt/2,0)
	t.fetch_find(x)
	v_median=eval("t."+fieldname)
	if mod(cnt,2)=0
		x1=t.fetch_next()
		v_median=(v_median+eval("t."+fieldname))/2
	end if
	median=v_median
END FUNCTION

[Ronald Anusiewicz]

Re: Find the median age of a group of people

G,

Thanks for your suggestion. Looking at Stan's code, I believe he took that same approach.

Tom,

Nice code and very well documented. I adapted your code to my live data and my results are now further from the excel spread sheet results.
I then populated your test table with the data from my live data and I got the same results.

excel 41.1 alpha 42.0 Stan's code gives me 40.5

Interesting. I'm not saying your method is wrong, I'm just puzzled as to why the two disagree by so much.
Why did you chose to invert the birthdate? It doesn't seem to effect the results as I tried it both ways.

I'm not sure the difference is enough to really matter, just curious.

In case your interested, I've attached a zip file with toms_tbl.dbf containing the same 20 records that I have been working with, both in Alpha and in excel.

Thank you,

Ron

[Tom Cone Jr]

Re: Find the median age of a group of people

I'll take a look at what you're doing. I don't know how Excel produces the median value.

I inverted the list of ages because its easier for me to validate the results that way, since I'm validating by hand.

[G Gabriel]

Re: Find the median age of a group of people

The correct answer is 42 and is produced both in alpha and Excel.