Problems with creating a group for a datawindow?

[kaul125]

I want to create a datawindow that has the following criteria:

Column – ‘Age Range’ – the values will be ’20 – 29 years’, ’30 – 39 years’, etc. all the way up to ’90 – 99 years’

Column – ‘Number’ – this column will have a value of the number of patients that fall within each group. For example, we have 2 patients that are within the age range of ’20 – 29 years’; we have 5 patients that are within the age range of ’30 –39 years’

Column – ‘Percent’ – this column will have the percent total for each age range group. For example, if we have a total of 7 patients and there are 2 patients in the ’20 – 29 years’ group, then the percent for that group would be 2 divided by 7 which equals .2857.

Our database does not have a field called ‘age’. This means I have to compute the age for each patient based on the ‘birthdate’ database field. I have no problem calculating the age. There are no intentions of storing the age in the database.

My problem is trying to create a datawindow with the above criteria, with the computed fields and then create a group that would produce the following format:

Age Range Number Percent
------------- ------- ---------
20 – 29 years 2 .2857
30 – 39 years 5 .7143
--------------
7 1.0000
Does anyone know of a way to create the grouping that would produce the above report format? I've tried looking at the 'Create Group' in the datawindow painter, but I could not figure out how to set up the group.

My datawindow SQL would only have the 'patient id' and 'birth date' fields selected. The fields I want to display on the report would have to be computed fields. Correct?

Keith

 

[Tentacle]

I suppose there are many solutions to this problem; however my solution would be as follows:

On your datawindow create a computed field called cf_date_range for instance. I take it from your question that you already have the age worked out, so let’s say you called it cf_age, in which case the expression for your computed field cf_date_range will be as follows:

if(cf_age >= 20 and cf_age <= 29, 1, if(cf_age >= 30 and cf_age <= 39, 2, if(…)))

So basically you are saying if the age of the record is between 20 – 29 make the value of the computed field cf_date_range 1 else if the record age falls between 30 – 39 make the computed field cf_date_range 2 and in short 40 – 49 will equal 3, 50 – 59 will equal 4 and so on until 90 – 99 will equal 8.

Next create a group on cf_date_range as well as sort by it. This will sort all the record by their date range, and all that is left to do is to place your data into the trailer group of cf_date_range. In other words you will know all the cf_date_range values equal to 1 falls within the range 20 – 29, 2 within 30 -39 and so forth. So all that is left to do is to count the records per group and don’t forget to make the detail height zero.

Hope this makes sense, if not let me know I’ll try and see if I can clear it up some more.

Kind regards

Tentacle

 

[kaul125]

I tried your suggestion and I would like to clarify some points because I'm not getting the result I would expect.

My query is the following:
select birthdate
from table_name
where certain_field = 1

I get a total of 5 records for this query. 4 cliets are in their 20's and 1 is in their 30's. I have many computed fields to calculate the age, plus the cf_date_range you mentioned. I put the cf_date_range in the trailer group portion of the datawindow, along with the age field. Now when I run the report, I only get back two records/rows, which would be correct because I have some people in the 20's and some in the 30's.

I'm assuming that the field to display '20 - 29 years' or '30 - 39 years' or etc. would be a computed field also that would be calc. as follows:

if(cf_date_range = 1,'20 - 29 years',if(cf_date_range = 2,'30 - 39 years,if(...)))

How is the field to display the # of records per group calculated? Is this a computed field, which would be: sum(cf_date_range)?


Thanks for the help

Keith

 

[Tentacle]

The easiest solution would be to place the following computed field in the trailer group for cf_date_range:

count(cf_date_range for group 1)

This should give you the total records per group, which you can then use to do your percentage calculation with.

Let me know if this solves your problem

Kind regards

Tentacle

 

[kaul125]

Thanks, the count(cf_date_range for group 1) worked fine.

However, I'm getting a strange result for the total column. I want to display the total # of patients per group at the bottom of the report. when I do a sum or count for the count(cf_date_range for group 1) column I get 16. Now the query behind the datawindow brings back 5 rows, 1 patient in the 20 - 29 group and 4 patients in the 30 - 39 group. So if I did a sum for this column I would think the result would be 5 and not 16. I put this new calculated field both in the trailer group and the summary portion of the datawindow. I still get the same result.

Do you know why I'm getting 16?

Thanks for the help.

Keith

 

[Tentacle]

Hi

To get the total amount of records you need to enter the following in you computed field:

count(cf_date_range for all)

that should bring back 5, why your total is bringing back 16 could be because it is summing the totals and not counting them. Let me know if this solved your problem.

Regards

Tentacle

 

[kaul125]

Hi,

Thanks your last suggestion regarding the total count worked! However, now I'm having trouble with the Percent total column. Here are the field I have and how their being calculated:

cf_date_range = If(c_age >= 20 and c_age <=29,1,if(c_age.....)) - in the trailer group
cf_count_per_grp = count(cf_date_range for group 1) - in the trailer group band
cf_percent = cf_count_per_grp / cf_total - in the trailer group band
cf_total = count(cf_count_per_grp for all) - in the footer band
cf_percent_total = count(cf_percent for all) - in the footer band

All fields above are calculated correctly except cf_percent_total. Using the same example in the previous thread, below is the result I get:

20 - 29 years 4 80.00%
30 - 39 years 1 20.00%
Total 5 500.00%

For the cf_percent_total, why does it calculate 500.00% instead of 100.00%?

Keith

 

[Tentacle]

Try summing the total instead of counting them like so:

cf_percent_total = sum(cf_percent for all) - in the footer band

According to your example that should add up to 100%

 

[kaul125]

Thanks for the input and everything works fine now!

Keith