Graph Average Doesn't Work

[SQLRory]

Hi all,

I have a Crystal V11 Report which has 3 Groups as per below

Grp 1Service Area
Grp 2Local Authority
Grp 3Quarter

In Grp 3 footer I have 7 formula fields which I am using to create a 7 series line graph in the report footer for Grp1. For the graph I am using data > layout advanced > on change of quarter > ploting > avg of @formulafields*
*represents the 7 different series
This plots the 7 lines I require however the averages don't add up.

The report seems to think that (-34+8+6+6-48)/5 = -27! and plots this instead of the true value -12.4

Could someone please advise if there something obviously simple I am missing which would cause this?

Thanks in advance

Rory
You'll never know everything, that's half the fun!

 

[IanWaterman]

Crystal will not do the Maths wrong!

It would indicate that the formula is not getting the values you expect.

Display all the formula fields on the report both where they are evaluated and where your final avg calculation occurs.

Ian

 

[SQLRory]

Hi,

I am fully aware that Crystal won't get a calculation wrong, that wasn't the point of the question.

I have done what you suggested however in order to this I will have to create a running total for this, because I am getting the average when the @quarter variable equals specific quarters to see if this marries up on the graph.

Funny part is it only allow me to use one of my 7 formula fields in the running total 'available tables and fields'. This is bizarre because I know they are all in the report!!!

This has well and truely got me.

Rory

 

[IanWaterman]

Sorry just me being ironic.

What you say here is a clue

I have done what you suggested however in order to this I will have to create a running total for this, because I am getting the average when the @quarter variable equals specific quarters to see if this marries up on the graph.

Why? surely if your avg is using a formula the formula must evaluate correctly at the point you calculate. Why do you need to replicate with an RT?

You can not create RTs on summary formula or vars.

Ian

 

[SQLRory]

I should have really known that given I worked with Crsytal for nearly 3 years, albeit self tought!

That prompts me with a huge problem though...

I need the report to give me the average of a specific formula field and then plot it per quarter on my graph.

Some background....
The formula fields give me the percentage of a specific ethnic group in my data. The reason it has to be formula fields and not just a normal summary is because the percentage isn't based on the population and is only to be calculated from a value @ethnic.

An example of the formula field:
if Sum ({@@ethnic}, {@quarter}) = 0 then 0
else Sum ({@whiteirish}, {@quarter})/Sum ({@@ethnic}, {@quarter})*100

@ethnic is nothing complex it just returns a 1 or 0 per record see below:
if {Command.Pop Category} in ["White","White Irish","Black","Asian","Chinese","Other Ethnic Group"] then 1 else 0

These all are placed in the Grp3 footer which groups by Quarter.

This gives me the percentage of the specific ethnicity in relation to the sum of the @ethnic formula field I created. The if is there because Crystal doesn't divide by 0 and will generate errors.

Shall I simply scrap the report. It is now becoming more hassle than it's worth! If anyone has any ideas I would be very appreciative.

Many thanks
Rory

 

[IanWaterman]

In that case use variables

Assign the value of the var using the same formulas

then in your group footer use the vars to calculate your Average.

eg
@var1
whileprintingrecords;

global numbervar var1;

if Sum ({@@ethnic}, {@quarter}) = 0 then var1:=0
else var1:=Sum ({@whiteirish}, {@quarter})/Sum ({@@ethnic}, {@quarter})*100

repeat for your 5 formulae

In group footer

@varavg
whileprintingrecords;

(global numbervar var1+
global numbervar var2+
global numbervar var3+
global numbervar var4+
global numbervar var5)


You will need to reset vars to zero in group header.

Ian

 

[IanWaterman]

Should of course been

@varavg
whileprintingrecords;

(global numbervar var1+
global numbervar var2+
global numbervar var3+
global numbervar var4+
global numbervar var5)/5

 

[SQLRory]

I see your point and thank you very much.

I haven't used vars before but get what these are doing. I'm sure this won't give me what I need though.