Distinct Count of Non-null Values

[Former Member]

I have a table that has one column for providerID and then a providerID in each of several columns if the provider is under a particular type of contract.

I need a distict count of each provider under each type of contract for every county in the US.

distinct count is almost always one more than the actual distict count because most counties have at least one provider that does not have a particular contract and the distict count counts the null value as a distict value.

I know I can alter the fields to have a zero for nulls, ask for a minimum count and then subtract 1 from the distict count if the minimum is zero, but I hope there is an easier way to figure distict counts of non-null values.

any suggestions?

Thanks,

Jennifer

[Former Member · Accepted solution]

Jennifer,

You can use the running totals that come with Crystal. You have to go to Evaluate-Use a Formula, click the box and say something like:

Not IsNull (TypeA_whatever).

Make a running total for each "type" of contract.

The Panda

[Former Member]

Both of your suggestions work if you have a single field for contract type, but as stated, there is a field for each contract type

State, County, Provider, Type A, Type B, Type C, Type D, Type E, Type F, Type G

There are 7 types of contracts and one provider can have just one or all 7

[former_member260594]

In File Rpeort Options select Convert null database values to default.

In the crosstab right click on the summarized field and enter a Display string formula similar to the following;

numbervar x;

if CurrentFieldValue <> 0 then x:= CurrentFieldValue;

totext(x)

[Former Member]

Hello,

*I need a distict count of each provider under each type of contract for every county in the US*

To the above requiremetn,

I will suggest the following approach.

Use group expert formula for country, contract and provider.

Now you will have the hierarchy to which level you want to apply distinct count. You can do it as suggested by ken hamady.

Regards

Usama

[Former Member]

A distinct count will not count true Nulls, but will count blanks and empty string values. You can do a distinct count of an IF-THEN formula that returns a null value. I have written an article on this problem:

http://www.kenhamady.com/form14.shtml

Edited by: Ken Hamady on Jul 12, 2008 7:55 PM