Averaging a sum field

[Zanz38]

Hello,
Back again!! I am trying to do an average figure of a formula "{@VMI Solution Availability}" by using the following text "Average ({@VMI Solution Availability})" and CR XI advises "this field can not be summarised"

"{@VMI Solution Availability}" is calculated using ({qry_VMI_Outages.Available_minutes} - Sum ({qry_VMI_Outages.Duration}, {qry_VMI_Outages.Month})) % {qry_VMI_Outages.Available_minutes}

What I am wanting to show is the yearly Average Availability made up of the monthly Availability figues. I have had a wee look around and can see this thread which was never actualy answered thread767-1333966.

My next problem when I have cracked that nut is to then show it on a graph with for example

Bar 1 2005 Average
Bar 2 2006 Average
Bar 3 2006 Dec Actual
Bar 4 2007 Jan Actual
through to bar 12 Nov 2007 Actual

so the graph will show all Year Averages then show the past 12 months data

Can anybody help me with step 1? even better if they can help with step 2

 

[IanWaterman]

YOu could try using a formula to group your data as indicated and then calculate avearge for each group.

If Year(Outages_date) <> year(currentdate) then totext(Year(Outages_date), 0,"") else
totext totext(Year(Outages_date), 0,"")&"/"&totext(Month(Outages_date),"00", 0,"")&"/

The second part will return dates group MM/YYYY, the "00" forces into Jan to display as 01 rather than 1, which is important when sort numeric text.

currentdate is a crystal function so type as is.

Ian

 

[IanWaterman]

Sorry Do not need &"/ on the end of the month formula.

Ian

 

[lbass]

The referenced thread did provide the solution, just not the specifics. You would create a formula:

whileprintingrecords;
numbervar aveavail := aveavail + {@VMI Solution Availability};
numbervar cnt := cnt + 1;

Place the above formula in the section where you are displaying your VMI Solution Availability formula.

Then in the year group footer, use a formula like:

whileprintingrecords;
numbervar aveavail;
numbervar cnt;
aveavail/cnt

Place a reset formula in the year group header:
whileprintingrecords;
numbervar aveavail;
numbervar cnt;
If not inrepeatedgroupheader then(
aveavail := 0;
cnt := 0
);

I doubt the results will be available for charting however.

-LB

 

[Zanz38]

Sorry about the delayed response, thank you I will have a crack at either of these solutions