sum of distinct counts 1

[zupnik]

Hi All!

Versions: CR 8.5 ; DB Access 2002

I am trying to get the following result:

# of Trailers

2004 33
January 15
1/2/04 6
1/10/04 7
1/15/04 2
February 18
2/1/04 6
2/12/04 12

The issue that I have is following:
The Field "Trailer" in the DB is a string and an example for a trailer would be FDSS12343. I need at least 3 Groups (year, month, day).


Q:If I do a distinctcount for GH3 (day),how can I get the Sum for the month (GH2).... Doing a distinct count for the month (GH2) DOESNT work because I can have the same trailer in multiple days. i.e if trailer FDSS12343 arrives on 2/1 & on 2/12, a distinct count would give me 1... but I need 2.

Thanks all for the help

 

[lbass]

The following will give you the sum for the month and the year:

//{@reset} to be placed in the month group header:
whileprintingrecords;
numbervar monthtot := 0;

//{@accum} to be placed in the day group header (or footer):
whileprintingrecords;
numbervar monthtot := monthtot + distinctcount({table.trailer},{table.date});
numbervar yrtot := yrtot + distinctcount({table.trailer},{table.date});

//{@displaymo} to be placed in the month group footer:
whileprintingrecords;
numbervar monthtot;

//{@displayyr} to be placed in the report footer:
whileprintingrecords;
numbervar yrtot;

-LB

 

[zupnik]

Thanks lb for the prompt response!

somehow I am getting bad results...

let me summarize (to see if I understood):

1) create 4 formalae
2) place these into the headers & footers in the design view

The results I get is: 0 for the month, and a running total for the days (it doesnt look as if the distinctcount took effect)

let me know if you need details

thx lb

 

[lbass]

Make sure you placed them in the sections I specified. Also you have to substitute your table and field names for {table.trailer} and {table.date}. If you continue to get bad results, please explain what you mean by that--and show sample results so that we can tease out what is going wrong.
This really should work as is.

-LB

 

[zupnik]

lbass,

what I forgot to mention (sorry I am so deep in this - for me its natural) is that I also have PO and Item. i.e


Date Trailer PO Item
2/1/04 A12345 100005 3443423
2/1/04 A12345 100005 4537638
2/1/04 A12345 100005 2734629
2/1/04 A12345 474888 3443444
2/1/04 A12345 474888 5888474
2/1/04 A23333 636388 3443423
2/1/04 A23333 243533 4537638
2/2/04 A65646 323654 2423974
3/2/04 A12345 484859 3435272

Above is what the 'raw' data looks like...

On the first trailer (A12345) I have 2 PO with 5 Items.
On the second trailer(A23333) I have 2 PO with 2 Items.
Please note that an Item can be on multiple POs

The result should be:
Trailer PO Item
Feb: 3 5 8
2/1 2 4 7 *
2/2 1 1 1
Mar: 1 1 1
3/2 1 1 1

2004: 4 6 9*

* although the item in records 1 & 6 are identical, NO distinct count should be used (they should be counted as two seperates) since they came on different trailers.

I hope I clarified this somewhat...

thx

 

[lbass]

For this to work, you must place the display formulas in the group footers, not the headers. You can suppress {@accum}. Your result would then look like:

Trailer PO Item
Feb:
2/1 2 4 7 *
2/2 1 1 1
Subtot: 3 5 8

Mar:
3/2 1 1 1
Subtot: 1 1 1

2004: 4 6 9*

Please provide feedback after you have placed the formulas in the right areas.

-LB

 

[zupnik]

lb

the problems lies on my side, i think i nailed it to one problem...

after the 'distinctcount', in between the () brackets is the field supposed to be the date, trailer ID or both??

//{@accum} to be placed in the day group header (or footer):
whileprintingrecords;
numbervar monthtot := monthtot + distinctcount({table.trailer},{table.date});
numbervar yrtot := yrtot + distinctcount({table.trailer},{table.date});

although i have been working with CR for a while, I am confused..

thax a lot again for your assistance

 

[lbass]

Substitute your trailer ID field for {table.trailer} and your date field for {table.date}. You must have a group on your date field, set to print for each day (but I think you have this).

-LB

 

[zupnik]

great! data is correct but it is in the form of a running total i.e

1/2 11
1/5 21
1/6 32
1/7 46

instead of:

1/2 11
1/5 10
1/6 11
1/7 14


we're gettting there...

apprecaite your help

 

[lbass]

You already have the daily results by inserting a distinctcount on {table.trailerID} at the day group level. You are supposed to suppress the {@accum formula} since this will show as a running total (that's what it is!). You then need to put the {@displaymo} formula in the month group footer, so you can display the results of the running total. You cannot put {@accum} in the group footer, since it would add the last value again--thus you need the display formula. Same for {@displayyr}--it belongs in the report footer.

-LB

 

[zupnik]

Thanks lb for your patience, it works as you described

Z.