Need help with grouping. 2

[SpeedRacer]

Group 1 is pond number
group 2 is week number

gf2 looks like this

week # # of days fed #'s fed for the week average feed

17 3 605 202
18 4 800 200
and so on.

I need the largest average of all weeks to show at the group 1(Pond#) footer.

 

[lbass]

It's hard to follow your layout, but try something like this. Create a formula and place it in the group #2 footer:

whileprintingrecords;
numbervar maxave;
if sum({table.#fed},{@weekno})/count({table.#days},{@weekno}) > maxave then
maxave := sum({table.#fed},{@weekno})/count({table.#days},{@weekno});

In the group #1 footer use a formula:
whileprintingrecords;
numbervar maxave;

In the group #1 header, add a reset formula:
whileprintingrecords;
numbervar maxave;
if not inrepeatedgroupheader then
maxave := 0;

-LB

 

[SpeedRacer]

The number of days fed is actually a count field being displayed at group 2 footer to give me the total number of days fed.

 

[lbass]

And? You mean you inserted a count on some field? All you really need to do here is to replace my GUESS at your formula for average:

sum({table.#fed},{@weekno})/count({table.#days},{@weekno})

...with your actual formula for average, so that the formula reads:

whileprintingrecords;
numbervar maxave;
if {@youraverage} > maxave then
maxave := {@youraverage};

The other formulas remain the same.

If this still doesn't help, please show the content of your average formula or describe how you arrived at the average.

-LB

 

[SpeedRacer]

Sorry for the lack of info...i'm kinda new to this please be patient with me.


i set a count on week number in the detail section resetting it on change of group. And placed it at GF2 level.
i placed a total of feed fed per day at gf2 and then divided total feed by number of days. that gives me the average per week. I need to take the largest week and show it on the gf1 level. There are a total of 43 weeks, i just need to be able to show the largest.

 

[lbass]

You should not be using running totals, if that is what you are doing. Your formula for average should look something like mine. Try creating one in the field explorer->formula->new and place it in the group #2 footer. See if it is calculating correctly. Then paste it into the thread.

-LB

 

[lbass]

Or, actually, you could use running totals, and then change the formula to:

whileprintingrecords;
numbervar maxave;
if {#totalfied}/{#daysfed} > maxave then
maxave := {#totalfied}/{#daysfed};

-LB

 

[SpeedRacer]

Here it is:

whileprintingrecords;
numbervar maxave;
if Sum ({Feed.Feed}, {@Week #})/{#Days Fed} > maxave then
maxave := Sum ({Feed.Feed}, {@Week #})/{#Days Fed}

Getting close, but still not right.

 

[CoSpringsGuy]

sweet!!!!

I followed along this thread and plugged the suggestion into a report I have been working and it worked to perfection.

I would suggest not using running totals as LBass mentioned initially.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

speedracer,

What results are you getting that are incorrect? Can you show a sample or explain in what way the results are incorrect? Does the following give you the correct average in the GF#2?

Sum ({Feed.Feed}, {@Week #})/{#Days Fed}

-LB

 

[SpeedRacer]

This is what it looks like now:

GH1 Pond# 114

wk# #/days fed tot lbs avg feed
gf2 17 3 1655 552
gf2 18 6 3260 0
gf2 19 5 2505 0
gf2 20 4 2240 560
gf2 21 6 4655 776
gf2 22 6 4295 0
And so on...

I'm getting these results for each pond. The report should only have 1 gf2 line for each pond. Which would be the week with the biggest Average. I tried using the Maximum function and it gives the running total cannot be created error.

 

[CoSpringsGuy]

That wasn't how i think I understood your initial problem to be. The solution that LBass gave should still work quite nicely for what you are trying to do.

An alternate solution if I may throw it out here could be to sort group 2 by average ascending
Report - Group Sort Expert - select the second tab - All - select your average on the right hand side - select ascending

When you go back the report your highest average should be the last G2 footer under each G1. Move the fields wk#, # of days fed, total lbs and the avg fed formula into the G1 footer.
Hide your G2 header and footer and details.

Each group 1 footer with then display the highest wk for each pond.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[CoSpringsGuy]

if you decide to try that method use a formula similar to the one below and place in G2 footer. for average as i dont believe running totals will work. This will also be the field you use to Group Sort G2 as explained above

//
sum({poundsfed},{week})
/
count({days},{week})

Again, the solution LBass gave should still give you the results you desire. Im just throwing out an alternate solution since your last post indicated you didnt need to see all the weeks for each pond in your report output.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[lbass]

Two comments. Your last explanation of what you want to see differs from your first, and I didn't intend for you to show the results of the first formula as the average. That formula should be placed in GF#2 and suppressed. A separate formula should be placed in the GF#1 to display the results. You can expand it to include the other fields if you want:

//{@accum} to be placed in GF#2 and suppressed:
whileprintingrecords;
numbervar maxave;
stringvar all;
if Sum ({Feed.Feed}, {@Week #})/{#Days Fed} > maxave then (
maxave := Sum ({Feed.Feed}, {@Week #})/{#Days Fed};
all := totext({@Week #},0,"")+chr(9)+
totext({table.days},0,"")+chr(9)+
totext({table.lbs},0,"")+chr(9)+
totext(maxave,0,"");

Then in the GF#1, use the following to display the results, and suppress GF#2, if you wish.
whileprintingrecords;
stringvar all;

-LB

 

[SpeedRacer]

Thanks so much....Sorry for all the confusion i caused.

your formula works great lbass.