Averaging Sums Within Group

[crystal123456]

Hello,

I am creating a report that pulls in products that are forecasted by our customers. The way my report works is the user inputs an end date, and the report goes out and pulls in all of the products that are forecasted up to that date.

On my report I display Part Number, Part Description, Quantity Forecasted, Date for each quantity.

The report gets grouped first by which line it runs on, then by the parts family part number, and then finally by date which I break up by month.

I had to sort by date because depending on the volumes on our product, we will forecast by week or by month.

My goal of this report is to get an average of each products forecast by month.

So, I Summed the quantities for the months under the month group.

Then to get what I am looking for I need to average these Sums for the month under the product family group.

When I try to average this field, Crystal will not allow me to do so and only gives options for Maximum, Minimum, Distinct count, etc...

I have tried creating formulas, but it gives an error and says "Field cannot be Summarized".

There is also no guarantee how many months will fall under a product family, so when averaging it will need to recognize how many months to average over.

Any help would be greatly appreciated, I am using Crystal 11 FYI

Thanks,

Michael

 

[lbass]

Place a formula like this in the month group header or footer and suppress it:

whileprintingrecords;
numbervar cnt := cnt + 1;
numbervar summo := summo + sum({table.qty},{table.date},"monthly");

In the product family group header, use a reset formula (suppress) like this:

whileprintingrecords;
numbervar cnt;
numbervar summo;
if not inrepeatedgroupheader then(
cnt := 0;
summo := 0
);

In the product family group footer, use this formula to display the average:

whileprintingrecords;
numbervar cnt;
numbervar summo;
if cnt <> 0 then
summo/cnt;

-LB

 

[crystal123456]

Lbass,

Thank you very much, this worked perfectly

 

[crystal123456]

Lbass,

I now need to sum up my averages for each family so I can see my totals under each line(the first group of the report).

I am again having a similar problem as I was before with the field not able to summarize but I am unsure how to apply your previous post to this.

 

[lbass]

Change the display formula to:

whileprintingrecords;
numbervar cnt;
numbervar summo;
numbervar ave;
numbervar sumave;
if cnt <> 0 then
ave := summo/cnt;
sumave := sumave + ave;
ave

Then in the line group header add a reset formula:

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

In the line group footer, add this display formula:

whileprintingrecords;
numbervar sumave;

-LB

 

[crystal123456]

Lbass,

Thank you again, this worked well.

I also trying to place a pie chart on the report showing this sum we just came up with on change of Line.

The formula doesn't show up when I go to the advanced portion of the pie chart creator, is there something else I need to do to be able to graph this number?

Thanks

 

[lbass]

Yes, you have to use the following method, which is somewhat complex:

http://www.sdn.sap.com/irj/boc/go/p...2a54ff4cd?QuickLink=index&overridelayout=true

You should lay out all of your requirements at the beginning of the thread, as sometimes it affects proposed solutions.

-LB