Averaging

[Jillp]

* I have a summary report that lists total hours for each job.

* There are 51 jobs.

* I would like to take 5 of them and do an average, take the next 5 and do an average etc., so that I can do a chart on each average of 5.

I am counting my records and have an 'initializecounter' so that I am counting each record and starting with 1 again after I reach a count of 5. I don't know where to go from here. Is there a way to group each 1 - 5 group to do averaging on them?

I write lots of reports with crystal but have no programming background.

Any help would be appreciated.

Jill

 

[FoxG]

Hi,

You could try a similar but different approach...

Create a formula which increases by 1 each time 5 records has been read.

Then group by this formula.

You should then be able to generate your summaries.

Hth,
Geoff

 

[mbarron]

Geoff,
Because I like a challege I tried to figure this problem out. I had the same idea that you suggest as far as creating the field to increase every 5 records.

What am I missing? I've was able to create a formula to increase by one for every 5 records but I cannot group on the field. I have it set up as a shared variable so that it changes on each record. but, because it is shared, it is evaluated "WhilePrintingRecords" and cannot by grouped.

Here is my formula:
shared numbervar recnum; //record number;
shared numbervar gcount; //group counter;
recnum:=recnum+1;
if remainder (recnum,5)= 1 then
gcount:=gcount+1 else gcount:=gcount


Mike

 

[kenhamady]

You can't group on a running counter, because the counter is created after the grouping.

Try this:
Create a formula field called {@test}:

WhileReadingRecords;
Numbervar test;
test := Test + 1

Then create a running total field that averages your value. Set the reset by formula using:

EvaluateAfter ({@test1});
NumberVar Test;
Remainder(test,5) = 1

Last, put this running total on a separate detail band (details B) and suppress the section using a formula:

Remainder({@test1},5) > 0
Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[FoxG]

Hi,

what i was looking at was...

global variable counter;
if remainder(recordcount,5) = 0 then counter = counter + 1
counter

You should then be able to group on the formula.

Will this not work?

Geoff

 

[kenhamady]

Geoff,

Don't know, what is recordcount?

Ken Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[FoxG]

Hi,

I meant RecordNumber didn't I !!

Geoff

 

[kenhamady]

I don't think it will work. You can try it. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Jillp]

You have all given me some great ideas but I still haven't been successful. Probably in my logic but......here's a sample of my report...

Detail (suppressed)
Job Op WC S/Uhr RunHrs TotHrs
A 50 Mach1 2 10 (Su+R)
A 60 Mach2 1 20 (Su+R)
sum
A 3 30 (SmS+R)
B (SmS+R)
C.. etc…..

I need to take my (SmS+R) from my summary line and average 5 of them.

Mike and Geoff’s examples don’t allow me to make a group on my counter ;

and if I am understanding Ken’s example I'm looking at details. Can’t go there.

I can’t do running totals on summary figures, correct?

Am I missing something?

Thanks, Jill
P.S. I am new to this forum, are these sort of questions okay?

 

[kenhamady]

It never hurts to ask, of course not all questions get answered.

You can't use the running total fields on Summaries, but you can use the variable technique to create running totals.

See the FAQ in the common forum on Running totals and use the 3-formula technique. What I wrote will now go in the Job footer instead of details. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[Ngolem]

perhaps I can suggest another approach

Why not create a subreport where all the values of the sums are calculated and stored in a shared variable array.

Then create a formula where these sums are then displayed sequentially in the detail section of the main report. You cannot group on these sums but you can display them and group using some other kind of criteria.

then make a chart from the sums.