Average on average - cannot summarize field 1

[AndersV]

Hello!

Im having trouble summarizing a formula field. Using version 10

AVERAGE(field1, field2) Im doing an average based on another field. It works fine but in the footer i want to do an AVERAGE again based on the formula field an on the count of the rows.

Like this:

0.2
0.3
0.4

---
AVERAGE(formulafield, count rows)

But ive realised that you cannot do this in crystal. So is there any other solution to do this problem?

Regards A

 

[Telemachus]

where have you placed the Average that you are calculating from the other fields? please provide more details

Thanks,
Madhu

 

[AndersV]

It is placed in a group header

Need more info?

 

[Telemachus]

I am naming the AVG function in the group header as AVG-1 for ease of explanation.
Create a shared variable inside a formula and intialise it in the report header.
now use sharedvar:=SharedVar+AVG1
place this formula in the group header
create another formula in the report footer as AVG-2 and create the expression sharedVar/(Count of rows)
the count can be taken from special fields or you can evaluate using another formula
This should give you the required average.
Please check if it works, else we could try something else

Thanks,
Madhu

 

[dgillz]

Madhu is close. Count of rows won't work since the avg formula is in the group header. You need a count of groups.

So add another variable to count the groups, then divide the 2 variables to get the average of the average.

Also 2 things 1) you do not need to initialize either variable, ans 2) the variables don't need to be shared unless you are going to pass info back and forth between subreports.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[AndersV]

Tried this today but didnt get it to work

Got it to print out a total that is correct but when just printint the value of the formula in the footer it was not close to correct!

Any other example ?

 

[AndersV]

Tried this today but didnt get it to work

Got it to print out a total that is correct but when just printint the value of the formula in the footer it was not close to correct!

Any other example ? Really basic would be nice

 

[lbass]

It is unclear what you are trying to do. ARe you really trying to average an average? Please show the content of the formula you want to average. Also provide your group structure, and show at what level you want to do the average. In other words, show a sample report that includes the groups and shows your expected results.]

-LB

 

[AndersV]

Like i said i do the AVERAGE(field1, field2) in the Group Header #1 and it works fine i get the result i want. In the report footer i want to take the sum of the reults and do an average of them. That is if i take the former numbers presented the result should be like this:

Group Header #1 - AVERAGE (field1, field2)
--

0.2
0.3
0.4

Report footer B (sum of the fields above / count of rows)
--
0.3 -- calculated ( 0.9 / 3 = 0.3)

Structure is Group Header #1 based on a field and in the Report footer B i want to do the average on the fields that have been averaged before.

This is done in a subreport for the report.

Please let me know if the problem is unclear and i will try to provide more information.

 

[Telemachus]

Along with the calculation of the sum for average
ie sharedvar:=SharedVar+AVG1
declare another variable 'sharedvar count'
and initialise it to 0
the concept is to evaluate the count as
count:=count+1, each time the average is summed.
and in the report footer calculate the average by dividing sum with count. Please try this and get back with the results.hope it works!

Thanks,
Madhu

 

[AndersV]

sharedvar:=SharedVar+AVG1

Is this the same variable ? or is sharedvar and SharedVar to different variables?

Do i have to use WhilePrintingRecords?

 

[Telemachus]

Hi Anders,

It is the same variable.
it is safer to use while printing records
Thanks,
Madhu

 

[AndersV]

Im doing as told

But when im doing this

sharedvar:=SharedVar+AVG1 ({@sharedvar}:={@sharedvar}+{@fAverage} in my example)

I have declared the sharedvar like this:
shared numberVar sharedvar

I keep getting the error "The remaining text does not appear to be part of the formula"

When im removing the : it works but the formula then turns into a boolean???

 

[lbass]

You should use these formulas:

//{@accum} to be placed in the group header:
whileprintingrecords;
numbervar grpave := grpave + {@fAverage};
numbervar cnt := cnt + 1;

Then in the report footer use this formula:
whileprintingrecords;
numbervar grpave;
numbervar cnt;
grpave/cnt

-LB

 

[AndersV]

That worked fine!!!

Thank you very much