Averaging 1

[33216CLC]

Hi,

I would like to obtain the average of several columns in my report. Each column contains a figure from a different field (but same table) of the datasource. I used a formula in this report to display the average but it comes up blank. This is a bit confusing because when I set up the formula to be used in the formula field, Crystal confirms that there are no errors in my formula.

Here is an example of the formula I used:

Average ([{Rpt_Pension.AnnPay1}, {Rpt_Pension.AnnPay2}, {Rpt_Pension.AnnPay3}, {Rpt_Pension.AnnPay4}, {Rpt_Pension.AnnPay5}])

Any idea what could be wrong? Thanks for any help.

 

[Siggy19]

Are any of those fields NULL ? I'm not sure whether Crystal ignores the NULL values or sets the whole formula to NULL for averaging.

For example;

9, 2, NULL, 4, 5

could either be 5 (if it ignores the NULL value altogether), 4 (if it treats it like a zero, but this is unlikely) or NULL.

 

[synapsevampire]

To eliminate those pesky nulls (unless you have a reason to use them), select File->Report Options->Convert Null Field Value to Default.

-k

 

[Siggy19]

Be wary of doing this... NULLs can be really useful.

For example, COUNT (and DISTINCT COUNT) ignore them so you can count the number of actual entities, for example if you use an OUTER join.

For example, using the query;

SELECT a.orderno AS Order_No, b.orderno AS Cancelled
FROM Orders a LEFT OUTER JOIN Cancels b
ON a.orderno = b.orderno

The total number of orders is DISTINCTCOUNT(Order_No) while the number of Cancelled orders is DISTINCTCOUNT(Cancelled)... if it has been cancelled, there will be a record in the Cancels table and a non-null value in b.orderno while if it is still live b.orderno will equal NULL.

Also, if Zero is your default alternative to NULL, you cannot then distinguish between genuine zeros and NULL values. Similarly with spaces. This can cause confusion, especially on select statements since I suspect that these are not converted. Thus, without any selection, the report might show 27 records with a value of zero. However, if you select only the records with a value of zero, there might be only 12 of them since the selection does not include the NULL values.

 

[33216CLC]

I thought of placing zeros in the blank fields and realized it would not help. What I need is for the formula to return the average according to the fields that are not blank. If one field is blank then 4 would be used for averaging. The 5 fields each represent a figure for a different year. The total of all 5 years should be divided by 5. If figures are present for only 3 than the total is divided by 3. If I try K's suggestion, the result would be incorrect.

It appears as if Crystal nulled the formula (if such a thing occurs).

Any other thoughts? Thanks.

 

[Siggy19]

Of course, you can always cheat...

Simply calculate your own average in a formula by using;

@TestAverage
numberVar Total:=0;
numberVar Counter:=0;

if not isnull({Rpt_Pension.AnnPay1}) then
Total:=Total+{Rpt_Pension.AnnPay1};
Counter:=Counter+1;
end if;

if not isnull({Rpt_Pension.AnnPay2}) then
Total:=Total+{Rpt_Pension.AnnPay2};
Counter:=Counter+1;
end if;

etc...

if Counter > 0 then Total/Counter else 0;

 

[lyanch]

You could just calculate the average yourself.. and handle the nulls...

Lisa

 

[33216CLC]

Thanks, Siggy. Both you and Lisa are suggesting exactly what I was trying to avoid; using code outside of Crystal to get what I want. It seems as though I may have to resort to doing just that.

Will see how it goes.

Thanks. You were a great help.

 

[lyanch]

You can calculate the average yourself in Crystal...

iif(isnull(field1),0,field1) + .../ if(isnull(field1),0,1)...


Lisa

 

[Siggy19]

Very nice. The only comment I would make is that it could crash with a "divide by zero" if all the values are null. Therefore I would use;

iif(isnull(field1),0,field1) + .../ ((iif(isnull(field1),0,1) + ... ) + 0.0001)

It makes the result slightly less accurate, but only by a negligable amount which will be swamped by any rounding done and guarantees that it will work.

 

[33216CLC]

Hi Siggy, Lisa,

I am not very familiar with what you are suggesting. I have used formulas in Crystal, but not like those you wrote in your last response. Could you please expound? I would really appreciate it.

Thanks

 

[33216CLC]

Siggy19,

I tried your suggestion (below) on a formula in crystal and I still get the average on 5 instead of on the actual amount of Annual Salaries. The only difference is that I did not use the End if - Crystal does not recognize it.


numberVar Total:=0;
numberVar Counter:=0;

if not isnull({Rpt_Pension.AnnPay1}) then
Total:=Total+{Rpt_Pension.AnnPay1};
Counter:=Counter+1;
end if;

if not isnull({Rpt_Pension.AnnPay2}) then
Total:=Total+{Rpt_Pension.AnnPay2};
Counter:=Counter+1;
end if;


It seem as though whether the field is null or not null, it is not recognized as null. Is this a bug in Crystal?

 

[Siggy19]

My apologies, I was using Basic syntax.

Using the Crystal Syntax;

numberVar Total:=0;
numberVar Counter:=0;

if not isnull({Rpt_Pension.AnnPay1}) then
(
Total:=Total+{Rpt_Pension.AnnPay1};
Counter:=Counter+1
);

if not isnull({Rpt_Pension.AnnPay2}) then
(
Total:=Total+{Rpt_Pension.AnnPay2};
Counter:=Counter+1
);

Notice the semicolons.

The problem with just getting rid of the End If is that you were basically saying;

if not isnull({Rpt_Pension.AnnPay1}) then
Total:=Total+{Rpt_Pension.AnnPay1};
Counter:=Counter+1;

since it was treating the IF as a single line statement. I had not realised that I was using the Basic Syntax and thought the End If worked for both.

Hopefully this will help.

 

[33216CLC]

Siggy19,

I did not know you had responded. For some reason I did not receive a notification. So I sought help elsewhere. The response I got was similar to your last post:

if not isnull({Rpt_Pension.AnnPay1}) then (
Total:=Total+{Rpt_Pension.AnnPay1};
Counter:=Counter+1; );

Thank you for your willing help.