Distinctcount is counting null values 4

[dataprincess]

I'm using CR7. The first formula gives me the serial numbers that match the criteria. The second formula is supposed to do a distinct count of those serial numbers. The result is it is counting the null values as a distinct "serial number". I've tried using if statements to exclude the nulls but this doesn't work either. Can someone please help me! I've been trying to figure this out for four days now.

FuncPass:
if {v_Detail_Report.TestType}="Functional" and {v_Detail_Report.EX}="PP"
then {v_Detail_Report.SerialNumber}

FuncPTotal:
distinctcount({@FuncPass},{@ParseWeek})

 

[kenhamady]

The problem is that when you don't give the formula an "else", the result is not a null, it is the default value for the THEN data type, which is an empty string ("&quot in your case. There is no way to output a null with a formula (unless you have a null field to input).

You can use a running total field to do your distinct count of the formula. Set it to evaluate based on a condition formula that says:
{yourformula} > "" Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[dataprincess]

So once it goes through the running total (which takes forever) how can I get the max number of that formula for use in another formula? Maybe I'm trying to do something that can't be done.

 

[kenhamady]

Here you would have to drop to using formula's with variables. Storing the current value from first group, compare to next group, store the higher of the 2, compare to next, .... to get the max. You Won't know the max until the end.

Can be done, but you are getting close to the limits of a 2-pass reporting tool. To get more passes, you would have to use subreports. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[Dond12]

I have been able to do something similar. Perhaps this will help.

I have a value in the details section of my report that is the result of a series of computations on other items in the same details section (i.e., 4*VALUE_ONE + 4*VALUE_TWO + 3*VALUE_THREE, and so on).

A separate formula in the report provides a running total of the first value. The thing looks like this:

VALUE_ONE VALUE_TWO VALUE_THREE COMPUTED_FIELD RUNNING_TOTAL

At the end of each group, I want to compute an AVERAGE of the COMPUTED_FIELD.

The problem is that, even if there are no values in a particular line for VALUE_ONE, VALUE_TWO, or VALUE_THREE, Crystal Reports puts a ZERO in the COMPUTED_FIELD.

What this means is that, even though the RUNNING_TOTAL does not increment by anything, the AVERAGE will be based on the assumption that every COMPUTED_FIELD actually exists (i.e., no COMPUTED_FIELD is null). But, we know that's not true.

So, I created a new accumulator formula that uses the following formula:

whileprintingrecords ;
if(isnull(VALUE_ONE)) then shared numbervar KOUNTER := KOUNTER + 0
else
shared numbervar KOUNTER := KOUNTER + 1 ;

What this gives me is a RUNNING COUNT of the instances when the values are not null.

Finally, I created a formula that simply divides the RUNNING_TOTAL by the KOUNTER.

The last value of this variable will be the real average. You do have to watch out for division by zero and other pitfalls, but on the whole this is a useful technique.

Create a RESET variable with the formula:

whileprintingrecords ;
shared Numbervar KOUNTER := 0 ;
shared Numbervar RUNNING_TOTAL := 0 ;
etc.

Put this formula in the group header and suppress it.

 

[dataprincess]

You know how some people make a mountain out of a mole hill. Well that's me. I tried most of your suggestions, but guess what worked.

I created a formula called @Null with absolutely nothing in it and referred to it in my formula. It worked! Don't know why but it did. Sometimes I'm too close to the forest. Thanks for all your suggestions.

FuncPass:
if {v_Detail_Report.TestType}="Functional" and {v_Detail_Report.EX}="PP"
then {v_Detail_Report.SerialNumber} else {@null}

 

[kenhamady]

WOW! When I tried it the formula that referring to {@null} errored, saying that it needed a string. So I put in "", saved the other formula, and then took the "" back out.

And, son of a gun, it worked.
That is pretty cool. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[slimshades26]

I guess because everyone unsure how this worked you may not be able to turn around and make this applicable to a number instead of a string. It keeps telling me a "number is required here" Thanks for any response. ss26

 

[synapsevampire]

Interesting workaround...

Perhaps converting null values to default and suppressing blanks might work too.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[slimshades26]

That would work but originally I was trying to make sure it did not count it. I made what I was trying to do work but was still interested in a response about this. Thank you for responding. I appreciate your time. ss26

 

[kenhamady]

If you follow my pattern, using zero instead of "" it might work. Worth a try.

1) Put zero in the second formula instead of "".
2) With the zero in place, save the first formula
3) Then go back and take out the zero from the second formula. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[synapsevampire]

Yep, that works, Ken.

And you can check for isnull(), so it takes it.

I'm not sure what this buys me, but it's intriguing.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com