Find the median age of a group of people

[Ronald Anusiewicz]

Re: Find the median age of a group of people

Thank you G, Tom and Stan

This is my final code in the reports OnPrintInit event.

G, I guess I missed your last function. I did take your advice and made the function universal. I did have a slight problem with the function. I believe you missed the order argument. Once I added the order, it worked. Actually, it's very fast. Especially since I'm running the function four times before opening the report.

Ron

'Date Created: 25-Mar-2013 12:11:05 PM
'Last Updated: 27-Mar-2013 10:53:08 PM
'Created By  : Ron
'Updated By  : Ron

'get the median age of members
'the users selects a start date and an end date from an xdialog which runs the report
'i.e.  start date: 01/01/2011   end date:  12/31/2011
'omit members with missing data in the birthdate and/or joined date and/or left date fields


'global vars set by an xdialog
'Var->vDate_Report_Start
'Var->vDate_Report_End


'Get median age of Active members
filter = "P_Status=\"A\".and.remspecial(dtoc(birthdate))<>\"\" .and.birthdate <= Var->vDate_Report_End .and."+between_date("birthdate",{01/01/1900},Var->vDate_Report_End)
var->vnActMedian = MedianAge("persons","birthdate", "birthdate", filter )			'Active

'Get median age of New members
filter = "P_Status=\"A\".and.remspecial(dtoc(birthdate))<>\"\" .and.birthdate <= Var->vDate_Report_End.and."+between_date("Date_Joined",Var->vDate_Report_Start,Var->vDate_Report_End)
var->vnJoinMedian = MedianAge("persons", "birthdate", "birthdate", filter)			'Joined

'Get median age of InActive members
filter = "P_Status=\"I\".and.remspecial(dtoc(birthdate))<>\"\" .and.birthdate <= Var->vDate_Report_End .and."+between_date("birthdate",{01/01/1900},Var->vDate_Report_End)
var->vnInActMedian = MedianAge("persons", "birthdate", "birthdate", filter )	'Inactive

'Get median age of members who Left
filter = "P_Status=\"L\".and.remspecial(dtoc(birthdate))<>\"\".and."+between_date("D_left",vDate_Report_Start,vDate_Report_End)
var->vnLeftMedian = MedianAge("persons", "birthdate", "birthdate", filter)	'Left


FUNCTION MedianAge AS N (tablename AS C, fieldname as C, Order as C, filter as C )
	lst=table.external_record_content_get(tablename,fieldname,order,filter)
	cnt=*count(lst)
	x=round_up(cnt/2,0)
	if mod(cnt,2)=1
		MedianAge=AgeX( ctod(word(lst,x,crlf()) ) ,Var->vDate_Report_End )
	else
		MedianAge=(AgeX(ctod(word(lst,x,crlf())),Var->vDate_Report_End)+AgeX(ctod(word(lst,x+1,crlf())),Var->vDate_Report_End))/2
	end if
END FUNCTION


FUNCTION AgeX AS N (Birthdate AS D, AsofDate as D )
	x=Months_Between(Birthdate,AsofDate)
	ageX=x/12
END FUNCTION


FUNCTION Months_Between AS N (Start AS D, End AS D )
	dim x as n
	x=if(end=month_end(end).and. day(end)<=day(start),1,0)
	while start<end
		end=addmonths(end,-1)
		x=if(end>start,x+1,x)
	end while
	months_between=x
END FUNCTION

[Ray in Capetown]

Re: Find the median age of a group of people

While this gets special merit from me

	lst=table.external_record_content_get(tablename,fieldname,filter)
	lst=sortsubstr(lst,crlf(),"A")
	cnt=*count(lst)
	x=round_up(cnt/2,0)
...
MedianAge=AgeX( ctod(word(lst,x,crlf()) ) ,Var->vDate_Report_End )

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.

still fascinating & ingenious - I havent tested it yet but will

[neosys]

Re: Find the median age of a group of people

I am looking for a solution to calculate the median for a field. What I have is a Report based on a preselected records within a set. The PDF report currently gives me the correct average for a group. I use grouping parameter for the total and count functions and then divide the total by count to give me the average. This gives me the correct average (mean) value. However, I would also like to calculate the median value.

Question: Any suggestions as to how this can be achieved as the UDF algorithms for median calculation above are all based on the table/set? Do I have to filter the table/set by the preselected records plus the group or is there an easier way to achieve the same.

[Ted Giles]

Re: Find the median age of a group of people

http://www.alphasoftware.com/alphafo...edian-and-Mode

[SNusa]

Re: Find the median age of a group of people

Data in a browse? (May work with several other objects too.)
=COMPUTE_FIELD_STATISTICS(<browse>.<field>).<property>
Lots of properties to choose from.

Oops... Just saw OP was for median/not mean.
Ted, your post reminded me to put reading glasses on lol.
(Still can't find delete post button though col!)

[neosys]

Re: Find the median age of a group of people

Hi Ronald

I have used your median function successfully when the function is called with a DBF file parameter i.e. median("ct.dbf","days"). However using the same function called with a SET ( i.e. median("ct.set","days") gives no result.

Can a SET be used with UDF?

Kind regards

Raj

[Stan Mathews]

Re: Find the median age of a group of people

The function evaluates records in a table (dbf). A set is not the same and thus can't be used with the function. You might be able to create a mapped table with the necessary fields from the tables in the set and use the function on that table.

In other words, the problem doesn't lie with sets and udfs. The problem is that this particular function wasn't designed to operate on sets.

[neosys]

Re: Find the median age of a group of people

Thanks Stan for your prompt response.