"Distinct Count" Summaries Counting Nulls 1

[Kibeth]

I am running a report to find out how many unique customers or companies placed orders during the last week. I have done this by using the fields "Order ID" and "Member ID" and formulas called IsCust and IsComp. The formulas are:
IF {VIW_ORDER_DETAIL_REPORT.MEMBER_TYPE} = 1[1=cust 2=comp] THEN totext({VIW_ORDER_DETAIL_REPORT.MEMBERID}) ELSE "". This shows the MemberID based on the member type (customer or company)

The data displays as follows:
|OrderID @IsCust @IsComp|
|88034 99906 |
|88059 70141|
|Total Orders 2 DistinctCount of VIW_ORDER_DETAIL_REPORT.OrderI]|
|Total Customers 2 DistinctCount of @IsCust|
|Total Companies 2 DistinctCount of @IsComp|

As you can see, there was only 1 customer and 1 company, and the distinct count is counting the "" as a unique entry. I tried this formula IF {VIW_ORDER_DETAIL_REPORT.MEMBER_TYPE} = 1[1=cust 2=comp] THEN {VIW_ORDER_DETAIL_REPORT.MEMBERID} so that it simply returned a null, and it was causing the same results. What I need to know is if there is a way I can get these numbers to show correctly. This report will be run on a monthly basis, with a total number of customers/companies for each month as well as each week.

Please let me know if there is a way to make this work, or if you need any additional information.

Thanks! - kibeth
************************************
I am using Crystal Reports XI Professional
I am referencing an Oracle database
I am using Windows XP sp2

 

[Turkbear]

Hi,
One way would be to change the formulas you are using to count..
Use 2 like these:

@IsCustomer
IF {VIW_ORDER_DETAIL_REPORT.MEMBER_TYPE} = 1 then
1 else 0

@IsCompany
If {VIW_ORDER_DETAIL_REPORT.MEMBER_TYPE} = 2 then 1
Else 0

SUM these formulas to get the count for each type//
( You need not show these on the report - you can supress their display withput affecting the usage)




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Kibeth]

This would be great, as long as the same customer/company didn't make two orders in the same time period. When that happens, they would be counted twice where we only want them counted once.
Thanks for your advice. Any other suggestions?
-kibeth

 

[nlaliberte]

I would build an array that encompassed a list of your customers and companies. Then build an if statement that checked to see if a null value was present in the array.

If yes, then Distinct Count - 1.

If no, then Distinct Count.

Make sense?

so

shared stringvar array cust;
customer:= {table.customer};
if "" in cust
then DistinctCount({table.customer}) - 1
else DistinctCount({table.customer})

try that...

 

[lbass]

First create a formula {@null} by opening and saving a new formula without entering anything. Then change your formulas to use {@null}:

IF {VIW_ORDER_DETAIL_REPORT.MEMBER_TYPE} = 1 THEN
{VIW_ORDER_DETAIL_REPORT.MEMBERID} else
tonumber({@null})

Repeat for other formulas. Insert distinctcounts on the formulas--the null values will not be counted. This approach is based on a Ken Hamady solution.

-LB

 

[Kibeth]

lbass-
When I try your solution I get an error message saying "The string is non-numeric." Any suggestions on how to fix that?

 

[lbass]

Remove the tonumber() from around {@null}. Since you used totext() around the field in your first post, I assumed the member ID field was a number. I guess it isn't, and therefore {@null} doesn't have to be converted.

-LB

 

[Kibeth]

Okay, this isn't making sense to me. If I use the formula
IF {VIW_ORDER_DETAIL_REPORT.MEMBER_TYPE} = 1 THEN {VIW_ORDER_DETAIL_REPORT.MEMBERID} ELSE tonumber({@null})
then I get the error "The string is non-numeric."
but when I use
IF {VIW_ORDER_DETAIL_REPORT.MEMBER_TYPE} = 1 THEN {VIW_ORDER_DETAIL_REPORT.MEMBERID} ELSE {@null}
I get the error "A number is required here" with the (@null) hilighted.

Thoughts?

 

[lbass]

Did you enter anything into the formula area when you created {@null}? Also, please check file->report options and make sure that you don't have "convert null to default values" checked. Also, is memberID ever null? Finally, please let your mouse hover over the memberID field and note the datatype that is shown in the tooltip.

-LB

 

[Kibeth]

1 - I did not enter anything in the {@null} formula
2 - "Convert null to default values" is not checked
3 - MemberID is never null
4 - The data type of MemberID is (number)

Thank you again for all of your help with this.
-kibeth

 

[lbass]

What is the datatype of {VIW_ORDER_DETAIL_REPORT.MEMBER_TYPE}? Are you sure it is a number, not a string?

-LB

 

[Kibeth]

MEMBER_TYPE is a number.

 

[lbass]

First I would try to recreate the two formulas {@null) and the if/then. Your errors make no sense unless you inadvertently entered something like a space into {@null}.

-LB

 

[Kibeth]

I don't know why that worked lbass, but it did the trick. Thank you so much for all your input and patience in helping me resolve this!