Sum of Group average 4

[talibm]

Hi all, I am trying unsuccesssfully to create a formula to sum the average of a field. I have created a worker productivity cross tab report which is based on services provided to our clients. One of the services we provide is group counseling. In a group there may be 10 clients and the total time for the service is 180 minutes or 3 hrs. The details section shows 180 minutes for each client, but the worker productivity is 3 hours. The report is grouped by worker, service code, start date, and total time. i need to sum the average for total which has been converted to hours.I created this formula for the group service:

If {SERVICECODES.SERVICECODE} = "Group" then
Average ({ACTIVITIES.TOTALTIME},{ACTIVITIES.TOTALTIME})/60.
It returns this:
Date start end no.Clients totaltime prod
3-1-08 9am 11am 10 1200 2hrs
3-2-08 1pm 4pm 5 900 3hrs

So for each group service i get the average or the actual productivity for the worker, but I can't get a sum of the average. Ive tried running totals but I can't get it to reset. How do I sum the results for productivity. I am trying to create a manual cross tab report but I can't figure out how to sum the average


Thanks

 

[hneal98]

Do you have a group band in your report?

 

[talibm]

I'm sorry but I don't know what a group band is. I'm rather new at this

 

[lbass]

I wonder why you are grouping on the total time field--if you do that, the average total time will be the same as the total time in that group.

Also if you use a conditional formula like:

If {SERVICECODES.SERVICECODE} = "Group" then
Average ({ACTIVITIES.TOTALTIME},{ACTIVITIES.TOTALTIME})/60

...you are saying to show the average for all service codes whenever the service code = "Group". Is this what you intended? Ordinarily, you would instead create a formula like this:

If {SERVICECODES.SERVICECODE} = "Group" then
{ACTIVITIES.TOTALTIME}

...and then insert a summary on this. Then you could convert the result to a string.

Anyway, you can sum averages by using a variable like this:

whileprintingrecords;
numbervar sumave := sumave + {@average};//where average is the average before conversion to hours as a string.

In the report footer use a formula like this:
whileprintingrecords;
numbervar sumave;
sumave/60

If you want results at a group level, you need a reset formula in the group header (and move the display formula to the group footer):

whileprintingrecords;
numbervar sumave;
if not inrepeatedgroupheader then
sumave := 0;

-LB

 

[talibm]

lbass thanks for your help. I tried your formulas and everything worked except this:
whileprintingrecords;
numbervar sumave;
sumave/60

Before I waste your time let me explain what I'm try to do. I want to create a manual cross tab report which is a worker productivity report. I want it to look like this

Worker Name

service type Jan Feb Mar April May and so on
group counseling 10 20 30 15 25
individual couns 25 18 25 10 20
case management 50 45 35
assessment 15 10 10

i was able to get al of this done using running totals with a formula for totaltime/60 and the following:
{ACTIVITIES.STARTDATE}>= Date (2007,08,01) and {ACTIVITIES.STARTDATE}<= Date (2007,08,31) and
{SERVICECODES.SERVICE} = "AOD - Case Management" and
{WORKERS.WLASTNAME} = "Tuttle"
for each worker and the corresponding month but it wont work like that for group services because of the reasons stated earlier, i.e., 10 clients in a 3 hour group is 3 hours of productivity instead of thirty which is why I tried using the average and then trying unsuccessfully to get the sum to place in the crosstab report. I apologize. Iam in over my head with this and I'm am grateful for any help that you can give.

 

[lbass]

There is an easier way, but can you please explain why you are grouping on totaltime? That doesn't make sense to me, since you could theoretically have the same service code on the same date, but at different times with the same totaltime. And as I said earlier, the summary at this level doesn't make sense to me either. Can you show some sample data for one person at the detail level so I can see what you are doing?

-LB

 

[talibm]

I couldn't figure out how to attach an edited page of the report so I'm keying in a sample. It only show the detail of a group activity.

8976 8/1/07 10am 12pm 120 2
11102 8/1/07 10am 12pm 120 2
15556 8/1/07 10am 12pm 120 2
4208 8/1/07 10am 12pm 120 2
8/1/07 10am 12pm COU05 4 480 120 2

10316 8/14/07 4pm 7pm 180 3
11429 8/14/07 4pm 7pm 180 3
11714 8/14/07 4pm 7pm 180 3
8/14/07 4pm 7pm COU05 3 540 180 3

21470 8/26/07 12:15pm 2:30pm 135 2.25
36987 8/26/07 12:15pm 2:30pm 135 2.25
8/26/07 12:15pm 2:30pm COU05 270 135 2:25

the first row is the client number start time and end of the group and the total time in minutes. at the end of that section is a group. It is grouped by date and also total time (not shown) because we have groups that start same date and time but not the same total time. The group service code COU05, number of client in the group, total time of the group, and the actual productivity that the worker is credited with for the activity. I hope that this gives you a sense of what I'm trying to accomplish. and thanks for your time and your expertise. tm

 

[lbass]

You don't need to do this in the report footer. I think what you really need is just to sum the totaltime once per total time group, and you can do that by simply adding the maximum, whether a group or individual activity. You can insert a group on worker, activity, date, and total time, and then create these formulas like these:

//{@reset} to be placed in the activity group header:
whileprintingrecords;
numbervar sumjan;
numbervar sumfeb;
numbervar summar; //etc for all months
if not inrepeatedgroupheader then (
sumjan := 0;
sumfeb := 0;
summar := 0 //etc. for all months
);

Then in the totaltime group section add this formula:

whileprintingrecords;
numbervar sumjan;
numbervar sumfeb;
numbervar summar; //etc.

if month({ACTIVITIES.STARTDATE}) = 1 and
year({ACTIVITIES.STARTDATE}) = 2008 then
sumjan := sumjan + maximum({table.totaltime},{table.totaltime}) else
if month({ACTIVITIES.STARTDATE}) = 2 and
year({ACTIVITIES.STARTDATE}) = 2008 then
sumfeb := sumfeb + maximum({table.totaltime},{table.totaltime}) else
month({ACTIVITIES.STARTDATE}) = 3 and
year({ACTIVITIES.STARTDATE}) = 2008 then
summar := summar + maximum({table.totaltime},{table.totaltime})

Then in the activity code group footer, you can use these separate formulas and divide by 60 to get hours:

whileprintingrecords;
numbervar sumjan;
sumjan/60

whileprintingrecords;
numbervar sumfeb;
sumfeb/60

whileprintingrecords;
numbervar summar;
summar/60

The suppress all sections but the worker group header and the activity group footer.

-LB

 

[talibm]

Thanks LB.

 

[talibm]

Hello LB, For some reason I get an error that says the remaining text does appear to be part of the formula and the second {ACTIVITIES.TOTALTIME}is highlighted. Also where should this formula field be placed, the group footer?
whileprintingrecords;
numbervar sumjan;
numbervar sumfeb;
numbervar summar;
numbervar sumapr;
numbervar summay;
numbervar sumjun;
numberVar sumjul;
numbervar sumaug;
numbervar sumsep;
numbervar sumoct;
numbervar sumnov;
numbervar sumdec;
if month ({ACTIVITIES.STARTDATE}) = 1 and
year ({ACTIVITIES.STARTDATE}) = 2008 then
sumjan := sumjan + maximum ({ACTIVITIES.TOTALTIME}), {ACTIVITIES.TOTALTIME})

 

[lbass]

The second {Activities.Totaltime} is the group condition. You said you had a group on this field. And the formula belongs in the group header or footer for this group. If you reread my post you will see that it tells you where to put each formula.

-LB

 

[talibm]

Hello LB, I review the formula and discovered a parenthesis after the first totaltime fields. The formula works great, thanks a million. The formula works fine for the group activies, but I don't think it works for the other services. What I need is the sum of the other activities in addition to the formula you gave me for the group activities. A manual crosstab is what I'd like to have so that I would have the service listed even if there was any activity to calculate. Is it possible to combine the formula that I started with or something like it with the group activity formula? Thanks for all your help

 

[lbass]

Please show sample data for other services at the detail level.

-LB

 

[talibm]

Hi LB, Here's spme sample data. The individual services unlike the group services are added together and the sum divided by 60 to get the hours for the type of service

John Smith (Worker)
Caseno. Start Date Start Time End Time Total Time
11419 7/2/07 10:00am 10:30am 30.00
11136 7/2/07 10:15am 11:15am 60.00
10023 7/2/07 1:00pm 2:30pm 90.00
11111 7/12/07 2:00pm 12:45pm 45.00
Case Management 225.00 3.75

90147 7/3/07 1:30pm 2:30pm 60.00
50024 7/5/07 5:30pm 6:00pm 30.00
80014 7/7/07 1:00pm 2:00pm 60.00
Individual Counseling 150.00 2.5

After getting these totals for workers grouped by service this makes up our productivity report.

 

[lbass]

I think your groups should be: worker, start date, and start time, not total time. Then change the accumulation formula to:

whileprintingrecords;
numbervar sumjan;
numbervar sumfeb;
numbervar summar; //etc.

if month({ACTIVITIES.STARTDATE}) = 1 and
year({ACTIVITIES.STARTDATE}) = 2008 then (
if {SERVICECODES.SERVICECODE} = "Group" then
sumjan := sumjan + maximum({table.totaltime},{table.starttime}) else
if {SERVICECODES.SERVICECODE} <> "Group" then
sumjan := sumjan + sum({table.totaltime},{table.starttime})
) else
if month({ACTIVITIES.STARTDATE}) = 2 and
year({ACTIVITIES.STARTDATE}) = 2008 then
(
if {SERVICECODES.SERVICECODE} = "Group" then
sumfeb := sumfeb+ maximum({table.totaltime},{table.starttime}) else
if {SERVICECODES.SERVICECODE} <> "Group" then
sumfeb := sumfeb + sum({table.totaltime},{table.starttime})
) else
if month({ACTIVITIES.STARTDATE}) = 3 and
year({ACTIVITIES.STARTDATE}) = 2008 then
(
if {SERVICECODES.SERVICECODE} = "Group" then
summar := summar + maximum({table.totaltime},{table.starttime}) else
if {SERVICECODES.SERVICECODE} <> "Group" then
summar := summar + sum({table.totaltime},{table.starttime})
);

Place this formula in the starttime group header or group footer. The other reset and display formulas should stay in the same sections they are already in.

-LB

 

[talibm]

Hello LB, Sorry for taking so long to reply, but I was given a task that had me out of town for several days. I tried to group by starttime and I get an error message that says that there must be a group that matches this field. this is what I have
maximum ({ACTIVITIES.TOTALTIME}, {ACTIVITIES.STARTTIME}) and when I look at the group name for starttime I have a date, 12/31/1899. It is not grouping by starttime. I tried to format the field without success. Thanks for your help.
tm

 

[lbass]

You have to insert the group (insert->group) before creating the formula that references a summary for this group.

If {activities.starttime} is a datetime, then choose this as your field to insert a group on (insert->group) and then choose "print on change of minute."

-LB

 

[talibm]

Hi LB, I was able to get7 the {activities.starttime} corrected and the group now shows the time in the group header, but I'll still get the error message that there must be a group that matches this field. I started from scratch and inserted groups for workers, {servicecodes.service}, {activities.startdate}, and {activities.starttime}. I added this portion of the formula
whileprintingrecords;
numbervar sumjan;
numbervar sumfeb;
numbervar summar;
numbervar sumapr;
numbervar summay;
numbervar sumjun;
numberVar sumjul;
numbervar sumaug;
numbervar sumsep;
numbervar sumoct;
numbervar sumnov;
numbervar sumdec;
if month({ACTIVITIES.STARTDATE}) = 1 and
year({ACTIVITIES.STARTDATE}) = 2008 then (
if {SERVICECODES.SERVICECODE} = "CDCOU05" then
sumjan := sumjan + maximum({ACTIVITIES.TOTALTIME}, {ACTIVITIES.STARTTIME}) else
if {SERVICECODES.SERVICECODE} <> "CDCOU05" then
sumjan := sumjan + sum({ACTIVITIES.TOTALTIME}, {ACTIVITIES.STARTTIME}))

and got the error message for this portion of the formulas
maximum({ACTIVITIES.TOTALTIME}, {ACTIVITIES.STARTTIME}).
what am I doing wrong?

 

[lbass]

Did you group on a formula instead of on the field {activities.starttime} in order to get the correct time display? If so, then that formula as to replace {activities.starttime} in the above formula as the group condition.

-LB

 

[talibm]

No I grouped on the field {activities.starttime} and chose print on change of minute.