counting issues

[fsinned]

I could really use some help. I have inherited some reports that people are asking to be modified. The main problem is I'm real green, just learning.

The current report has 5 tables and usage comparison to current year vs prior, on hand and avail and incoming by product. The end user would also like to see the number of customers currently using the product. I had to add 1 more table to get the info. I need to have it look at each product and count the number of customers that have purchased it.

I tried "if {produnit01.prodno}={cardx01.prodno} then DistinctCount ({cardx01.custno}) else 0" but it gives a strange answer and it gives it over and over. I went from 329 records to over 400000 records.

 

[lbass]

I’m assuming you added the cardx01 table to get the customer number. If you linked it using a left outer join from the produnit01 table to the cardx01 table, then you don’t need to use a conditional formula to get the results. If you have a group on product number, you can just insert a distinct count on customer number at the product group level. Or, you could insert a crosstab in the report footer and add product number as the row field, and a distinctcount of customer number as the summary field.

Note that a conditional formula should take the form of:

If a = b then
{table.customernumber} else
Tonumber({@null}) //assumes the customer number is a number, not a string

...where {@null} is a formula that you create by creating a new formula in the field explorer, but entering nothing into it and then saving the formula. This avoids the value of zero being counted once for all default cases.

You would then insert a distinctcount on the above the conditional formula. The way you have currently constructed it, it just says if the condition is met for this product number, show the distinctcount for ALL products—which will be the same number repeatedly. But I don’t think you need a conditional formula, if you have linked correctly.

If you need more help, you should describe the group structure of the report and provide a little more information about the issues you are running into.

-LB

 

[fsinned]

lbass, Thank you for the help, but I still cant get it to work. I have pulled the 2 tables out and wanted to see if I can get it to work in a new report. Here is what I've got:
tables - cardex01 and produnit01
In both tables there is prodno and they are linked (because the other report uses multiple tables and they are all linked to prodno). I want to count the number of custno from the cardex01 table for each prodno. There are over 17 million entries total and it is showing them all. I want it to show something like this:

prodno custno
cpo-60 5
cpo-80 2

But I'm getting something more like

prodno custno
cpo-60 5
cpo-60 5
cpo-60 5
cpo-60 5
cpo-60 5
cpo-80 2
cpo-80 2

 

[lbass]

It looks like you have created a formula for the count and then put it in the detail section. You should be inserting a group on product number. In the detail section, place the customer number field and then right click on it->insert summary->distinctcount at the product number group level. Then suppress the detail section. You can drag the summary field to the group header to get the desired display. Suppress the group footer, too.

-LB