How to do a Disctinct Count for records belonging to a Group

[Schnieds]

Hi,

I have a report that I have created where I group all of the records on the report based on a "CreatedDate" field in month segments. I want to write some formulas to perform Distinct Counts on different records, but I only want the records whos "CreatedDate" column falls within the month segment belonging to the Group.

How can I write a formula to only do a distinct count on records which have a date in the Group's current month?

i.e.
if {record.CreatedDate} Between Group.StartDate AND Group.EndDate then
{record.recordID}

 

[lbass]

You don't need formulas for this. Right click on the field you want to do a distinctcount on->insert summary->distinctcount->choose the group level for the summary.

-LB

 

[samcneal]

I am using this formula to calculate the sum of the distinct loans:

currencyVar TVOL;
numberVar lcount;// := distinctcount({Data_Extraction.Loan Amount});
stringVar loannum := ' ';



For lcount := 1 to distinctcount({Data_Extraction.Loan#}) Do

(

If loannum = {Data_Extraction.Loan#} Then Exit For;
If {Data_Extraction.Loan#} = next({Data_Extraction.Loan#}) Then



TVOL := TVOL + {Data_Extraction.Loan Amount};
lcount := lcount + 1;
);



TVOL

But it is getting past the first record, therefore it is multiplying the first record loan amount 11 times or the count of the distinct loans in the table.

Can someone assist?

 

[synapsevampire]

I think that one of us doesn't understand something here...

A loop is generally used at the detail level, to iterate through some field, in your case you apparently think that it can be used to iterate through all of the fields in a table, which it cannot.

LB demonstrated the distinct count properly, but what you want is NOT a distinct count, rather a sum of some field providing a certain criteria is met.

I urge you to NOT attempt architecture, rather state your environment and requirements, using real technical terms:

Crystal version
Database/connectivity used
Example data
Expected output

I think what you want is probably just a Running Total field with a reset at the group level and your criteria in the evaluate->use a formula rather that trying to code this.

-k

 

[rrgkanth]

You can use a running total.

In the running total expert,

give the field to summarize - loan number,

select distinct count for type of summary,

evaluate using a formula {record.CreatedDate} Between Group.StartDate AND Group.EndDate

reset on change of group