Counting Distinct by a Group 1

[monkeylizard]

I am having a problem counting unique records.
I want to count the number of unique Record#'s by the Flag value.

Sample of Data

RecordNum  SequenceNum  Flag  User
100         1            Y     Bob
100         2            Y     Bob
100         3            Y     Bob
200         1            N     Bob
300         1            Y     Bob
300         2            Y     Bob
300         3            Y     Bob
300         4            Y     Bob

In this case, I want to know that I have 2 distinct Records where the Flag is Y and 1 where the flag is N

I have two formulas in a group header that is grouped by the RecordNum as follows:

(Formula 1)
If FLAG = Y and Then {RecordNum}
(Formula 2)
If Flag <> Y Then {RecordNum}

When I do a distinct count of Formula1 and Formula2 in a group header above the record number (actually the User name) I am getting the count +1. In the example data, I get 3 and 2 respectively instead of 2 and 1.

This only happens to Formula 1 if there are N records, and to Formula 2 if there are Y records. If there are no N records (the usual case), then a Distinct Count of Formula 1 yields the proper result. It only reports one extra if there are also 'N' records. Regardless of the number of 'N' records, if there is at least one 'N' then Formula1(the one that counts the distinct 'Y') is always 1 too high. And vise-verse.

I am assuming that when it reads through Crystal is tossing a <NULL> value into the list for the formula when it hits a record that doesn't match its criteria.

I can't just subtract 1 from the Distinct Count, because both are right as long as the other Flag value doesn't exist for the user.

Any advice on this?



Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[1973kk]

create a running total. email me the report if possible with saved data

 

[lbass]

Distinctcount does not pick up on null values--it is picking up on the default value for records that don't meet the criteria, i.e., 0 if recordnumber is a number or "" if it is a string. Note that if you do a count on a formula it will return the number of records, since it is counting each time the formula executes. A distinctcount will give you both the distinct count of records meeting criteria and any records that don't meet your criteria will be lumped together as the default and also counted.

You can either use a running total where you do a distinctcount of record number where you use a formula for evaluation:

(
onfirstrecord or
{table.recordno} <> previous({table.recordno})
) and
flag = "Y" //or "N" for the other running total

Reset never.

The other method would be to create a new formula called {@null} where you open the formula and save and close it without entering anything. Then you could use your original formulas, amended as follows:

//(Formula 1)
If FLAG = Y and Then
{RecordNum} else {@null}

//(Formula 2)
If Flag <> Y Then
{RecordNum} else {@null}

You could then insert distinctcounts on these and get the correct totals. This is based on a Ken Hamady tip.

-LB

 

[monkeylizard]

The running total is killing me. Too many subreports I guess. It's been running for 3+ hours and still no output. Can you give me some more info on the @NULL formula? I understand what I do with it on Formula1 and Formula2, but not how to make the @NULL formula itself.

Thanks

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[lbass]

Open a new formula and save and close without entering anything into the formula.

-LB

 

[monkeylizard]

Works perfectly. Thanks.

Have a star!

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.