Running Total/Distinct Count issue

[Shtinky]

CR 8.5
INTERBASE

I'm having an issue with running totals using distinct counts. I am writing a report that includes running totals based on freight bill numbers in order to find the number of shipments per customer in our database for a selected year (parameter). I have created a RT for the entire year as well as RT's for each month, but the totals never match.

HERE IS MY RUNNING TOTAL FOR THE ENTIRE YEAR:
Name: #bill count
Field to Summarize: {TLORDER.BILL_NUMBER}
Type of Summaryistinct Count

Evalute X-2 = year({TLORDER.BILL_DATE}) = {?year}
{?year} being a parameter

Reset on change of group

Placed in GF1

HERE'S MY RUNNING TOTAL FOR EACH MONTH:
for january:
Name:jan
Field to Summarize: {TLORDER.BILL_NUMBER}
Type of Summaryistinct Count

Evalute X-2 = month({TLORDER.BILL_DATE}) = 1 and
year(({TLORDER.BILL_DATE}) = {?year}

Reset on change of group

Placed in GF1

The other 11 months are done in the same exact way and here is what the data looks like:

TOTAL JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
5140 787 445 421 289 332 425 282 386 548 495 300 440

Let me do the math for you:
The total for all 12 months added up is 5150 distinct records, but the total for the year is only 5140. This is my issue. I have double-checked my work and my totals should match theoretically.

Has anyone encountered this kind of problem before or have any explanation on why this occurs? Any feedback is greatly appreciated. Thanks

the shtinkster

 

[lbass]

I think the problem is that some bill numbers are appearing in more than one month, so that while there is one distinct instance within a particular month, it could also appear in another month and accurately be counted again as a distinct instance of that bill number within that month. Taking the year as a whole, the same bill would only be counted once.

The question then becomes what data you want to show. You could accurately present the data above along with a note explaining that because some cases appear in multiple months, the total is less than the sum of the monthly data.

-LB

 

[Madawc]

You could also do a subreport that would spot cases where the same bill number appears in more than one month. Group by bill number, and count cases where the bill number is the same but month({TLORDER.BILL_DATE}) differs from month(previous({TLORDER.BILL_DATE}))

Madawc Williams
East Anglia, Great Britain

 

[Shtinky]

Thanks lbass and Madawc for your comments and ideas

shtinky