Distinctcount is counting null values

[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})

 

[mbutch]

Try adding ISNULL(fieldwithnullvalues)=false to your if statement.

 

[TiggerMom]

I am having the same problem. The distinct count doesn't work because the report puts in a zero for a false if statement so when the distinct count is done, it is counting the zeros once. What we need is to have the zeros show as null so the distinct count doesn't count them. If I find a solution I will pass it on; however, so far no such luck.

 

[IdoMillet]

You could create a running total, using Distinct Count as the Type of Summary and an Evaluate expression of
-------------
NOT IsNull({your_table.your_column})
-------------

Cheers,
- Ido ixm7@psu.edu

 

[TiggerMom]

I am trying to count distinct clients by gender.

In my details section, I have these 3 formula in a fields:

Field "MaleCount"
If {vDistinctClientPostalCode.GenderGlobalCode} = "M" Then
{vDistinctClientPostalCode.ClientID}

Field "FemaleCount"
If {vDistinctClientPostalCode.GenderGlobalCode} = "F" Then
{vDistinctClientPostalCode.ClientID}

Field "UnknownCount"
If {vDistinctClientPostalCode.GenderGlobalCode} = "U" Then
{vDistinctClientPostalCode.ClientID}

The above results in something that looks like:
Males Females Unknown
354 0 0
0 343 0
0 0 523
0 783 0

In the footer of my different groups, I do 3 distinctcount summaries on the above fields. The distinctcount counts the zero. I don't want the zero to be counted. So right now the above ends up looking like:

Males Females Unknown
354 0 0
0 343 0
0 0 523
0 783 0
--- --- ---
distinctcount: 2 3 2

I want it to read:
Males Females Unknown
354 0 0
0 343 0
0 0 523
0 783 0
--- --- ---
distinctcount: 1 2 1

Any ideas?

 

[IdoMillet]

You can use the same running total approach I described above without the need for intermediate formulas.

The evaluate condition for the first distinct count would be:

NOT IsNull({vDistinctClientPostalCode.GenderGlobalCode}) AND {vDistinctClientPostalCode.GenderGlobalCode}= "M"

Cheers,
- Ido


ixm7@psu.edu

 

[dataprincess]

Please refer to Crystal Reports - formulas for the solution. I figured it out a while back. Also I wrote a FAQ on this subject. Hope it helps.

 

[TiggerMom]

The data field is not null. There is data there. I am trying to identify whether the data says "M", "F", or "U" so that I can count up all the males, females, and unknown genders. I need to get rid of the zeros in my report so that the distinctcount does not count the zero.