Adding a distinct count

[pleashelp]

Using CR XIR2 with a SQL database. I have grouped my report by a date field by the week. I am displaying a distinct count of persons by the week. This part works fine of course. Now I want to add those weekly counts. I don't just want a distinct count of the month since that gives me a different number. I would like to actually add each weekly count to get a monthly total. Is there a way to do this?

 

[lbass]

You can use a variable. Create a formula like this:

//{@accum} to be placed in week group header or footer and suppressed:
whileprintingrecords;
numbervar motot := motot + distinctcount({table.field},{table.date},"weekly");

//{@display} to be placed in the group footer for month (if it exists) or the report footer:
whileprintingrecords;
numbervar motot;

If you have a month group, add a reset formula to the month group header:
whileprintingrecords;
numbervar motot;
if not inrepeatedgroupheader then
motot := 0;

-LB

 

[pleashelp]

Thanks so much.. BY putting the reset formula in the footer for the week I was able to just have the days for each week total and not accumulate for the month. This is exactly what I wanted it to do.

Now one more thing along the same lines....Is there a way to take that weekly total and get an average for each day. The caveat is that I only want to use days where there is a count of persons. Not all days have activity such as weekends and holidays. So not every week has the same number of days.

 

[lbass]

What? You said you wanted to add weekly totals to arrive at a month total. You cannot put the reset formula in the week group footer and have this work correctly. What exactly did you do? Please also lay out the entire problem from the beginning--please do not string one issue after another.

-LB

 

[pleashelp]

So sorry I didn't explain myself better. Here is what I did .

Group Header 1: date field grouped by each week (This header is displayed)
Group Header 2: same date field grouped by each day (This header is suppressed)
@accum variable (suppressed)
Group Header 3: Person Name (Suppressed)
Detail section: Suppressed
Group Footer 3: Suppressed
Group Footer 2: This section displays the Distinct # of Persons for the day
Group Footer 1a: @display (This footer is displayed)
Group Footer 1b: @group reset variable (suppressed)


So the results that I have are as follows:
GH1: Week of 11/1/09
GF2: Distinct count of persons for 11/2/09: 12
GF2: Distinct Counts of Persons for 11/03/09: 17
GF1a: # of Persons for Week of 11/1/09: 29

GH1: Week of 11/08/09
GF2: Distinct count of persons for 11/8/09: 3
GF2: Distinct Counts of Persons for 11/10/09: 24
GF1a: # of Persons for Week of 11/8/09: 27

 

[lbass]

Change {@accum} to:

whileprintingrecords;
numbervar motot := motot + distinctcount({table.field},{table.date});
numbervar cnt;
if distinctcount({table.field},{table.date}) > 0 then
cnt := cnt + 1;

Then in the week footer use this formula for the average:
whileprintingrecords;
numbervar motot;
numbervar cnt;
motot/cnt

Change the reset formula to:
whileprintingrecords;
numbervar motot := 0;
numbervar cnt := 0;

Since the reset formula is in a group footer, you don't need the test for repeated group header.

-LB

 

[pleashelp]

Thanks for your help!!!