Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

counting issues

counting issues

counting issues

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.

RE: counting issues

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.


RE: counting issues

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

RE: counting issues

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.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close