Eliminate zero from summary calculation

[warpped]

I have a report that does group summaries. The values that are summarized can be string or numeric. At the detail level, I check for one or the other. If it is numeric, I convert to a number, and then summarize Average and Standard Deviation. The problem is that when it is not a number, it still converts it but makes it zero, thus changing the summaries by factoring the zeros. I have tries suppressing if zero, but it still includes them in the calculation. Is there any way to ignore zeros from the summary?

Any help would be appreciated.

 

[lbass]

You could use running totals, e.g., select {@field}, average, with an evaluation condition of:

{@field} <> 0

If this isn't satisfactory then please provide your conversion formula and some sample data.

-LB

 

[warpped]

The data looks something like this...

Item Test Result1 Result2 Result3
5464 Q123 12.5 12.6 12.3
8465 Q123 11.2 11.2 11.3
9595 Q987 OK OK OK
6585 Q987 OK OK OK
7574 Q323 .23 .22 .21

The result fields are string.
So I have a formula in the detail section that checks to see if "Result3" is numeric. If it is, then it takes an average of Results1,2 and 3. If is not numeric it checks Result2, if Result2 is numeric, it averages Results1 and 2. It Result2 is not numeric, it checks Result1, if it is numeric it prints Result1. If none of them are numeric, it prints Result1. The end result, "ResultAvg" is string. I have another formula that converts "ResultAvg" to a number if it is numeric and greater than zero. This is the field that I need to summarize on based on "Test". The problem is that if it is not numeric, or = 0, then it still adds zero to the summary. I have the formula field set to suppress zeros.

 

[lbass]

From your description of the formulas, ResultAvg should be available in the running total expert. I'm assuming that you mean you want to summarize for each "test", so first group on test. Then use the running total expert and select ResultAvg, average (or whatever your summary is), evaluate based on a formula:

ResultAvg <> 0

Reset on change of group (test). If you can't group on test you could reset on change of field (test), as long as you sorted by the test field.

If this still doesn't work for you, please copy the contents of your formulas into your post.

-LB

 

[warpped]

Thanks for your reply.

I am currently doing doing that, here are the formulas


//@TESTAVG,located in the detail section - Determine of the
//value is String or Number. If number, then average them
IF NUMERICTEXT({Result3}) THEN
TOTEXT((TONUMBER({Result1}) + TONUMBER({Result2}) + TONUMBER({Result3}) )/3)
ELSE

IF NUMERICTEXT({Result2}) THEN
TOTEXT((TONUMBER({Result1}) + TONUMBER({Result2}))/2)
ELSE

IF NUMERICTEXT({Result1}) THEN
TOTEXT(TONUMBER({Result1}))
ELSE
{Result1}
//End formula

//@ResultAVG, located in the detail section - If @TESTAVG
//is numeric and >0 then convert it to a number
if numerictext({@TESTAVG}) and tonumber({@TESTAVG}) > 0 then
tonumber({@TESTAVG})
//End formula

//@STDDEV, located in the Test Group - Get the Standard
//Deviation of @ResultAvg
if {@ResultAvg} > 0 then
StdDev ({@ResultAvg},{TEST} )
//End formula

Thanks again!

 

[lbass]

I can't recreate your problem. You should be using {@ResultAvg} as your field in the running total, choose a summary, add {@ResultAvg} <> 0 into the evaluation section, select the appropriate reset. For the standard deviation, don't use a formula, use a running total again--again select {@ResultAvg}, standard deviation, add {@ResultAvg} <> 0 into the evaluation section, and select the reset.

This should work. If it doesn't, please explain specifically what error you are getting, or give an example of the results you are getting and another example of what the results should be.

-LB

 

[warpped]

I'm not getting an error, it runs OK. What's happening is that the Standard Deviation is incorrect because it is factoring in zeros.

Using the above numbers (I have to modify it)...
Item Test Result1 Result2 Result3
5464 Q123 12.5 12.6 12.3
8465 Q123 11.2 11.2 11.3
8365 Q123 NULL NULL NULL (No test performed)
9595 Q987 OK OK OK
6585 Q987 OK OK OK
7574 Q323 .23 .22 .21

1st average each test...
Q123 12.46
Q123 11.23
Q123 NULL NULL NULL (No test performed)
Q987 OK
Q987 OK
Q323 .22

Then convert to numbers where numeric...
Q123 12.46
Q123 11.23
Q123 0.00 (No test performed)
Q987 0.00
Q987 0.00
Q323 .22

Then take Standard Deviation of each group...
**Should be this**
Q123 .87
Q323 0

**Get this**
Q123 6.87
Q323 0

Because the test was not performed on one particular item, it was null. Converting to number, it becomes 0. I need to eliminate the zero from the calculation. I've tried suppressing nulls and zeros, but it is still factored in.

 

[lbass]

Are you using the running total for the standard deviation as I suggested? Suppression will not change calculations.

-LB

 

[warpped]

Thanks for your help lbass!

I ended up changing my query to eliminate the NULL values.