Cross Tab by Client's Age

[Rich24]

Hi all,

I'm sure this is pretty easy, but...

I'm trying to create a report to total immunization given (immun.immtype), by date administered (immun.servdate), and break that down by age.

I created 3 formula fields - Adult: "((immun.servdate) - (patient.dob)) / 365.25 >= 18"; Teen: "((immun.servdate) - (patient.dob)) / 365.25 >= 13 and "((immun.servdate) - (patient.dob)) / 365.25 < 18"; and Child: "((immun.servdate) - (patient.dob)) / 365.25 < 13".

In the Cross-Tab Expert I put "immun.servdate" in the columns, "immun.immtype" in the Rows, and "Count of @Child", "Count of @Teen", and "Count of @Adult" in Summarized Fields. I do not receive any errors. The Preview screen shows the correct number of imms per date and per type, but it repeats the same number 3 times in each cell. Example: Flu for 9/30/2010 has 32 Child, 32 Teen, and 32 Adult. 32 is the correct total number of Flu shots we administered that day for all age groups combined. It is not breaking it down by age.

Clear as mud? Any advice would be greatly appreciated.

Thanks, Rich

 

[Madawc]

To get the difference between two dates in years, use a formula field with DateDiff. Your HELP command should give you the DateDiff options.

Display this field with the raw data, to check it is working as you expect.

Then split using a single formula field: CHild is less than 13, Teen is 13 to 17 and others are Adult.

Do a cross-tab on that field.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

Use Ken Hamady's formula for an accurate age:

http://www.kenhamady.com/form06.shtml

Then create a formula like this:

if {@age} < 13 then
"Child" else
if {@age} < 18 then
"Teen" else
"Adult"

Use this as the column field in the crosstab, and use distinctcount of PatientID as the summary field, with the type of immunization as the row field.

-LB

 

[Rich24]

OK, I got the DateDiff to work.

Madawc, I don't understand what you mean by "split using a single formula field".

I created the following formula (CTA) and put it in Columns, but it only shows the clients <13:

if ({@Age} < 13) then
"Child" else
if (({@Age} < 18) and ({@Age} >=13)) then
"Teen"
else
"Adult"

How do I get Teen and Adult to show?

Also, LB, I need to break this down by date. That is why I originally put immun.servdate in Columns. Am I asking too much for a Cross-tab?

Thanks again!!

 

[lbass]

Datediff is not the way to go for {@age}--it's more complex than that.

You can put the {immun.servdate} as the row field and then click on options and choose whatever interval you want (if other than by day), add {@age" as the column field and distinctcount of PatientID as the summary. If you are only getting "child" to display then it's either your {@age} formula (please show what you actually used) or your record selection formula or your table linking.

-LB

 

[Madawc]

What I meant by "split using a single formula field" was that you should sort it into one of the three, as you've done.

If you're not getting the results from @Age that you expected, try displaying it and see what it is finding.

As lbass says, you need a complex formula to get the age accurately.


Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[Rich24]

Thank you all! It is now working just fine. I really appreciate all of your help>

LB, thanks for the Ken Hamady link. There's a ton of great info on his site.

Rich