very tough distinct count formula

[Halfcan]

HI,
I'm stumped. I have a somewhat complicated formula,
each record in my database contains these fields:

Ticket#, Network(string), IP (string), DateAdded, DateRemove
1 A-Network 192.168.1.1 01/01/03 11/01/03
2 A-Network 192.168.1.2 01/01/03 05/01/03
3 B-Network 192.168.1.3 01/05/03 10/01/03
and so on....

40 to 50 different networks, containing around 2000 Ip addresses.
This is a Quarterly report, and I have to break it down
to give the numbers for month 1, 2, and 3. (each month in the quarter)

What I need to do is distinct count the Networks if they pass the date calculation below.

@(1st month in Quarter)
if {@DateRemovedChecked} >= {@QuarterStart} and {DeviceList.Date Added } <= {@EndMonth1date} then DistinctCount ({DeviceList.Network})

@(2nd month in Quarter)
if {@DateRemovedChecked} >= {@Beginning2ndMonthDate} and {DeviceList.Date Added } <= {@EndMonth2Date} then DistinctCount ({DeviceList.Network})

@(3rd month in Quarter)
if {@DateRemovedChecked} >= {@Beginning3rdMonthDate} and {DeviceList.Date Added } <= {@QuarterEnd} then DistinctCount ({DeviceList.Network})

These formulas seem to work for when certain quarters and years are choosen. Either they all come up with the same number for each month, or the first month will have the correct number, and the 2nd and 3rd months will be zero.

any help or ideas on how to do this a better way would be greatly appreciated.

Thanks,
Andy

 

[Naith]

DistinctCount(x) counts all the records in the record set. It does not increment the count by 1 for each record meeting the criteria, as your code seems to think.

Use a running total condition to increment for the first month. Repeat the process for each of the subsequent months.

Or use variables like this:

@(1st month in Quarter)
if {@DateRemovedChecked} >= {@QuarterStart} and {DeviceList.Date Added } <= {@EndMonth1date}
then NumberVar 1stMonth := 1stMonth + 1
else 1stMonth := 1stMonth;

and adjust the other two formulae accordingly.

Naith

 

[Ngolem]

how is your data grouped? Also how is it displayed? We need to know both of these answers to properly advise you.

I will assume that you are grouped on Network and are printing the results in the footer.

You cannot use distinct count in this manner


Now in the group header for Network put the following formula (suppressed)

//@GrabNetwork

WhilePrintingRecords;
numberVar Q1cnt;
numberVar Q2cnt;
numberVar Q3cnt;

if not inrepeatedGroupHeader
(
Q1cnt := 0;
Q2cnt := 0;
Q3cnt := 0;
);

now in your counting formulas (i suppose in a detail section

//@(1st month in Quarter)

WhilePrintingRecords;
numberVar Q1cnt;
if {@DateRemovedChecked} >= {@QuarterStart} and {DeviceList.Date Added } <= {@EndMonth1date} then
Q1cnt := Q1cnt + 1;

@(2nd month in Quarter)
WhilePrintingRecords;
numberVar Q2cnt;
if {@DateRemovedChecked} >= {@Beginning2ndMonthDate} and {DeviceList.Date Added } <= {@EndMonth2Date} then
Q2cnt := Q2cnt + 1;

@(3rd month in Quarter)
WhilePrintingRecords;
numberVar Q3cnt;
if {@DateRemovedChecked} >= {@Beginning3rdMonthDate} and {DeviceList.Date Added } <= {@QuarterEnd} then
Q3cnt := Q3cnt + 1;

Now in the footer you display your results (the column headers being in the group header...this is the formula for Q1cnt...the others are the same.

//@displayQ1cnt

WhilePrintingRecords;
numberVar Q1cnt;

Q1cnt;


You do the counts separately to get a nice edge to the columns.

You could also have these results summarized in a report footer if the main report is used for other reporting. Then you would use arrays to store the data for later printing....a little more complicated but easily done.

But we don't know how you want this done. You give us not info.


Jim Broadbent