Distinct count for number of records selected

[elf1]

Hello,

I am creating a report using Crystal Decisions 8.5. I have a grouping defined called Division. The detail is capturing invoice numbers within a particular date range. This part is working fine.
I have done this with a formula
If Transaction Date is in date (2002, 10, 01) to date (2002, 10,31) then Invoice Number

In the footer of the grouping, I have added a Summary (Distinct Count), I would like to tally up the number of Invoice numbers that are being displayed for the date range.

I have 3 date range columns Oct 2002, Nov 2002 and Dec 2002.

The Oct column displays the correct number of invoice numbers the other 2 columns are adding 1 to the count.

What else should I be doing?

I appreciate your help.

Thank You,
Lisa

 

[mbarron]

Are you sure it's not off by one for October too?

It's probably giving you the extra one distinct count because it's counting the zero's (for the records that didn't occur in that month) as well as the invoice numbers. Mike
If you're not part of the solution, you're part of the precipitate.

 

[elf1]

Hi Mike:

Thank you for the response. I double checked- the October count is correct.

The other columns are adding 1 to the total.

Thanks again.

Lisa

 

[mbarron]

Here's a possible solution if my assumption was correct:
if minimum({one month ago}, {trans date}, "monthly&quot=0
then DistinctCount ({one month ago}, {trans date}, "monthly&quot-1
else DistinctCount ({one month ago}, {trans date}, "monthly&quot

Where {one month ago} is the field you are doing your distint count upon, {trans date} is the group, and "monthy" is the condition of the grouping



Extra formulas:
Here are some formulas that will calculate "months back" so that you don't have to edit each of the fields at the change of a month.

One Month Ago
//last month's transactions
datevar last1back;
datevar first1back;

last1back:=date(year(today),month(today),1)-1;
first1back:=date(year(last1back),month(last1back),1);

if {trans date} in first1back to last1back then {invoice.number}


2 months ago (for more than months ago- change the number in red to 1 less than the number of months back you want - i.e. for 3 months ago use 2)

datevar last2back;datevar first2back;datevar hold2;
hold2:=date(dateadd("m",-1,today))-1 ;
last2back:=date(year(hold2),month(hold2),1)-1;
first2back:=date(year(last2back),month(last2back),1);

if {trans date} in first2back to last2back then {invoice.number}

Use different variable names for each month back. Such as:
datevar last3back;datevar first3back;datevar hold3; for 3 months back

Mike
If you're not part of the solution, you're part of the precipitate.

 

[elf1]

Hi Mike:

Tried the formula example but to no avail. Same results.

I really appreciate your help.

Any other suggestions or options?

Thank you.

Lisa

 

[mbarron]

Would you be able to send the file (data seved with report of course) to me so I could look at the set up? I can't think of a reason why it would be giving you an extra 1.

vonbarron1@yahoo.com Mike
If you're not part of the solution, you're part of the precipitate.

 

[mbarron]

Lisa,
I've sent your file back. (The fields in purple are the ones I added)

I had made the assumption that you were returning a number when I suggested using;
if minimum({one month ago}, {trans date}, "monthly&quot=0 I should have asked what kind of field you were returning. Since it was resulting in a string field, you need to check for a minimum of ""

Below is the formula I ended up using (for October):
if Minimum ({@October Count}, {API_InvoiceHistory.Division})="" then
DistinctCount ({@October Count}, {API_InvoiceHistory.Division})-1 else
DistinctCount ({@October Count}, {API_InvoiceHistory.Division})


oct manual distinct count Mike
If you're not part of the solution, you're part of the precipitate.