Find the median age of a group of people

[Tom Cone Jr]

Re: Find the median age of a group of people

And I get 42 using my script against the 20 records in Toms_Tbl in Ron's second data set.

Ron, if you invert the birthdates and examine them in the default browse (toms_tbl), you'll see that the "middle" two records in the list are in the 10th and 11th positions. These are 1/5/73 and 12/15/68 respectively. These correspond to "ages" of 40 and 44, so the midpoint is half way in between, or 42, as G correctly observes.

I think you're asking the OnInit of the report to do too much heavy lifting, and the report engine is displaying the layout before your calculations are finished. Put your calcs in functions and use calc field values in the report, instead.

[G Gabriel]

Re: Find the median age of a group of people

Here is yet another shortcut and I think this one is much better than the previous ones:
Put these records in an array.
Sort the array.
Go to the element in the middle. This should be much easier and faster than anything else because here you could use the array index.
If you want, I could put this in a function too.

[G Gabriel]

Re: Find the median age of a group of people

And here is the function, tested and produces 42.
For simplicity purposes, I added a calc field to Tom's table, called it "Age" which calculates the age but you could modify the function to take an expression instead of a field name.

FUNCTION Median AS N (tablename AS C, fieldname as C, filter=null_value() as C )
	lst=table.external_record_content_get(tablename,fieldname,filter)
	cnt=*count(lst)
	dim a_lst[cnt] as C
	a_lst.initialize(lst)
	a_lst.sort("A")
	x=round_up(cnt/2,0)
	if mod(cnt,2)=1
		median=val(eval("a_lst[x]."+fieldname))
	else
		median=(val(eval("a_lst[x]."+fieldname))+val(eval("a_lst[x+1]."+fieldname)))/2
	end if		
END FUNCTION

And seems pretty fast. It ran in .005 sec on the 20 records table. That's certainly not a realistic number of records, but you could try it on a large number and see.

[Tom Cone Jr]

Re: Find the median age of a group of people

An array ? Sounds like a good idea to me. In fact, that's the approach I took, though G is able to build his array in fewer steps. Nice going!

[G Gabriel]

Re: Find the median age of a group of people

Sorry Tom, I didn't look at your script. I only used your table instead of building a new one.

[Tom Cone Jr]

Re: Find the median age of a group of people

No problem. Your contribution to the thread is valuable in its own right. Thanks.

[Ronald Anusiewicz]

Re: Find the median age of a group of people

G and Tom,

Thank you both very much for your help.

At this point, my puzzlement has to do with my version of excel and why excel is coming up with a different number. I do believe the error is in excel and my formula.

Anyways, the report is working fine and the OnPrintInit seems to be fine. I've attached the code I ended up using.

G.

I liked your code, very compacted, but although the filter would work when creating the queries, the query would not be used when running the computations. The queries were being generated as opening the table and selecting Query/Indexes, the queries were there and they worked properly. I tried adding the line i = tbl.index_primary_put("MedianR"), but the code would run against the whole table.

I have to admit that every time I think I'm starting to get a handle on creating and using queries, I run into something that breaks them and I can't figure it out. So when I get something that works, I give a sigh of relief and move on.

Thank you,

Ron

'Date Created: 25-Mar-2013 12:11:05 PM
'Last Updated: 26-Mar-2013 10:06:25 AM
'Created By  : Ron
'Updated By  : Ron

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

ui_msg_box(vnActMean,vninactmean+"   -   "+vnleftmean)

FUNCTION MedianR as N (Tablename as C, Status as C, sDate as D, eDate as D )
	'Date Created: 26-Mar-2013 09:11:09 AM
	'Created By  : Tom Cone
	
	IF Status = "L" THEN
		filter = "P_Status="+s_quote(status)+".and.remspecial(dtoc(birthdate))<>\"\".and."+between_date("D_left",sDate,eDate)
	ELSE
		filter = "P_Status="+s_quote(status)+".and.remspecial(dtoc(birthdate))<>\"\" "
	END IF
	
	
	' begin by opening the data table
	tbl = table.open(tablename)
	query.description = "MedianR"
	query.order = "invert(birthdate)"
	query.filter = filter
	query.options = "T"
	qry = tbl.query_create()
	rec_count = qry.records_get()
	i = tbl.index_primary_put("MedianR")
	
	'rec_count = tbl.records_get()
	ui_msg_box("rec_count",rec_count)
	
	tbl.fetch_first()
	dim ages[rec_count] as N	'populate an array of ages
	FOR j = 1 TO rec_count
		ages[j] = age(tbl.birthdate)
		'trace.writeln("Age: " + ltrim(str(ages[j])))
		tbl.fetch_next()
	NEXT j
	
	tbl.close()	  ' don't need table any longer, ages have been extracted
	' and stored in array
	
	IF mod(rec_count, 2) = 1  THEN
		' array has odd number of elements
		' median value is the middle element
		vn_middle = int((rec_count + 1) / 2)
		vn_median = ages[vn_middle]
		medianR = ltrim(str(vn_median))
		trace.writeln("The median age of " + ltrim(str(rec_count))+" ages is: " + ltrim(str(vn_median)))
	ELSE
		' array has even number of elements
		' median value is the average of the two middle elements
		vn_mid_low = int(rec_count / 2); vn_low_age = ages[vn_mid_low]
		vn_mid_high = int(rec_count / 2) + 1; vn_high_age = ages[vn_mid_high]
		vn_median = ( vn_low_age + vn_high_age ) / 2
		MedianR = ltrim(str(vn_median, 6,2))
		trace.writeln("The median age of " + ltrim(str(rec_count))+" ages is: " + ltrim(str(vn_median, 6,2)))
	END IF
	
END FUNCTION




















'FUNCTION medianR 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 = "MedianR"
'	query.order = "birthdate"
'	query.filter = filter
'	query.options = "NTM"
'	qry = tbl.query_create()
'	recs = qry.records_get()
'	i = tbl.index_primary_put("MedianR")
'	tbl.fetch_first()
'	IF mod(recs,2) = 1 'odd count
'		FOR qx = 1 TO int(recs/2)
'			tbl.fetch_next()
'		next
'		medianR = 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
'		medianR = combined/2
'	END IF
'
'END FUNCTION

[G Gabriel]

Re: Find the median age of a group of people

Make sure you are using Median not Mean in Excel.
If you have filtered the table already, it doesn't matter, just use the same filter in the function and it should work.

[G Gabriel]

Re: Find the median age of a group of people

OK..I see that you are using Median in Excel.
The difference is because the age function in alpha is not producing the fractions produced in Excel.
The Age function in alpha produces an integer, years only. Your calculation in Excel produces fractions, hence the Median is different, hence the results are different.
Apples & Oranges.
Are you going to ask how to get age in alpha with fractions?
I think I posted such function a while back..look it up.

[G Gabriel]

Re: Find the median age of a group of people

OK..before you ask, here is the function:

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

[Ronald Anusiewicz]

Re: Find the median age of a group of people

G and Tom, your making my head spin. This is great.

I'm not done yet.

Ron

[G Gabriel]

Re: Find the median age of a group of people

OK..One more shortcut and I will quit:

FUNCTION Median AS N (tablename AS C, fieldname as C, filter=null_value() as C )
	lst=table.external_record_content_get(tablename,fieldname,filter)
	lst=sortsubstr(lst,crlf(),"A")
	cnt=*count(lst)
	x=round_up(cnt/2,0)
	if mod(cnt,2)=1
		Median=val(word(lst,x,crlf()))
		else
		Median=(val(word(lst,x,crlf()))+val(word(lst,x+1,crlf())))/2
	end if
END FUNCTION

[Al Buchholz]

Re: Find the median age of a group of people

OK..before you ask, here is the function:

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

Months_Between() is a UDF found is this thread where a long discussion was held awhile back...

http://msgboard.alphasoftware.com/al...Months_Between

Looks like there is some rounding here too... YMMV..

[Ronald Anusiewicz]

Re: Find the median age of a group of people

This is what I ended up with for code in the reports OnPrintInit event.

The code works fine and the four different median results pretty much match median results in an excel spreadsheet.

The only hangup with this code is that the external_record_content_get returns the members birth date. I then need to convert the birthdate to an age. G added a field to the table which gave the persons age, but I do not want to add a field to the table.

I tried to create a calculated field value to be returned, but I didn�t have any luck getting it to work.

The *for_each function works, but it takes time to go through the list adding a 5-10 second delay. My table has 400 records however, the table may have up to 5,000 records.

Before reverting back to Tom�s code, is there something simple that I�m missing?

Ron

var->vnActMean = Median("persons", "A", "birthdate", "birthdate", vDate_Report_Start, vDate_Report_End )	'Active
var->vnInActMean = Median("persons", "I", "birthdate", "birthdate", vDate_Report_Start, vDate_Report_End )	'Inactive
var->vnLeftMean = Median("persons", "L", "birthdate", "birthdate", vDate_Report_Start, vDate_Report_End)	'Left
var->vnJoinMean = Median("persons", "AJ", "birthdate", "birthdate", vDate_Report_Start, vDate_Report_End)	'Joined

'ui_msg_box(vnActmean,vninactmean+"  -  "+vnleftmean)

' "(p_status = \"L\"  )  .and. isnotblank(\"birthdate\") .and. (between(d_left,ctod(\"05/18/2011\"),ctod(\"11/08/2011\")) )"


FUNCTION median AS N (tablename AS C, Status as C, Fieldname as C, order as C, sDate as D, Date as D )
	IF Status = "L" THEN
		filter = "P_Status="+quote(status)+".and.remspecial(dtoc(birthdate))<>\"\".and."+between_date("D_left",sDate,Date)
	ELSE IF Status = "AJ"
		filter = "P_Status=\"A\".and.remspecial(dtoc(birthdate))<>\"\" .and.birthdate <= Var->vDate_Report_End.and."+between_date("Date_Joined",sDate,Date)
	ELSE
		filter = "P_Status="+quote(status)+".and.remspecial(dtoc(birthdate))<>\"\" .and.birthdate <= Var->vDate_Report_End .and."+between_date("birthdate",{01/01/1900},date)
	END IF
	
	lst=table.external_record_content_get(tablename,Fieldname,order,filter)
	newLst = *for_each(foo, AgeX(ctod(foo),date),lst)
	
	cnt=*count(newlst)
	dim a_lst[cnt] as C
	a_lst.initialize(newlst)
'	a_lst.sort("A")
	x=round_up(cnt/2,0)
	if mod(cnt,2)=1
		median=val(eval("a_lst[x]."+fieldname) )
	else
		median=(val(eval("a_lst[x]."+fieldname))+val(eval("a_lst[x+1]."+fieldname)))/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

[G Gabriel]

Re: Find the median age of a group of people

The last function I posted is the simplest, most straight forward and most intuitive. If I were you, I'd use that one. Take a look at it.
I am in a big hurry today, so I will only respond to the part about getting a calc field returned:
lst=table.external_record_content_get(tablename,eval(\"AgeX(\"+Fieldname+\")\"),order,filter) by doing that, you do not need the *for each routine
I think you should leave Median() function without the extra arguments, e.g. sDate, status etc..Those you could incorporate in another function that utilizes the Median() function, that way you could use the Median() function generically across your applications.