I'm working on a report that displays new members that joined within a certain time frame. As members who have renewed or rejoined would have multiple lines in the Member table, the definition of a new member would be that the total count of a unique constituent id would be one. I've been researching Count, DistinctCount and ways of pulling unique fields but I can't seem to find a solution.

I cannot strictly use the "status" of the membership (join, rejoin, renew) as constituents often have different membership types that join and expire in overlapping time frames.

For those that are visual, the basic explanation below would only Constituent ID 10001 as a New member:

CONSTITUENT TABLE
ID
10001
10002
10003

MEMEMBERSHIP TABLE
ConstituentID Membership ID
10001 10
10002 11
10002 12
10002 13
10003 14
10003 15

I'm working on a report that displays new members that joined within a certain time frame. As members who have renewed or rejoined would have multiple lines in the Member table, the definition of a new member would be that the total count of a unique constituent id would be one. I've been researching Count, DistinctCount and ways of pulling unique fields but I can't seem to find a solution.

I cannot strictly use the "status" of the membership (join, rejoin, renew) as constituents often have different membership types that join and expire in overlapping time frames.

For those that are visual, the basic explanation below would only Constituent ID 10001 as a New member:

CONSTITUENT TABLE
ID
10001
10002
10003

MEMEMBERSHIP TABLE
ConstituentID Membership ID
10001 10
10002 11
10002 12
10002 13
10003 14
10003 15

try this formula:

If(totalcountID)=1 then "new" else "n/a"

Then use the select expert on the formula field to show only those who are new.
I think you could then create a total count on all the "new" lines

I was not able to use your exact formula, but I did use a modified version.
First, I created Groups: Records.Constituent_ID then added the formula

Unfortunately, Crystal XI does not allow to use a display string as part of the Select Query or as a Summary field (to get a total count).

Instead, I added a count to each Group (Records.Constituent_ID).

Using my example data that resulted in
REPORT
Constituent ID Count
10001 1
10002 3
10003 2

Then, as you had recommended, put a Select Expert statement on that count:


which resulted in

Constituent ID Count
10001 1

But now I've realized that this won't work for members that were "new" a year ago as they have since renewed and technically have two lines. :-/ I'm now working on a query that says something to the extent of "find all constituents that have a membership where Joined on date is between (First of the month, last date of the month) AND no joined on date before the first of the month." Ideally these would be organized by month.