Distinctcount/Sum(DistinctCount) Calculation in Each Group

[xmj0908]

My scenario is like this, I grouped by person and then by date, but there are a lot of records in one day, and now I only care about distinct count of days under each person, i used

shared numbervar sumofd:=sumofd + DistinctCount ({date}, {people})

and then display

local numbervar totalcount:= shared numbervar sumofd(say it is 9)

in the report footer so that I get a overall sum of distinct count of days.

The questions is that i need to go back to each group and do a calculation DistinctCount ({date}, {people})/totalcount(eg.9)

I just don't know how to do that becasue when I go back to each group, it is giving me the running total as the denominator.

Eg.
PersonA
9/1 3.00
9/2 4.00
9/3 3.58
9/10 4.08
Distinctcount of Date for A = 4

PersonB
9/3 8.67
9/9 6.76
9/13 1.90
9/17 2.68
9/25 3.50
Distinctcount of Date for B = 5

Total DistinctCount = 9

now I want calculate for A=4/9 and for B=5/9
instead, it is giving me A = 4/4 and B = 5/9

I am still a new user of Crystal Report, this problem has bothered me for a long time, I really appreciate if someone can help me to solve this. Thank you so much.

 

[Madawc]

You're coding things that are better done using Crystal's automatic totals, in this case a summary total.

The use of Crystal's automated totals is outlined at FAQ767-6524. In your case, the issue is that running totals 'run' with the data processed so far, while summary totals are complete for the whole report or group.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[xmj0908]

Madawc,

Thank you very much for your reply. The problem is that I can't just do a sum(distinctcount), it is not supported in Crystal Report, that's why I set a variable. Do you have any other suggestions? Thanks.

 

[lbass]

There are two alternate approaches you could use. You could save the report as a subreport and place it in the report header of the main report, and then set up a formula in the report footer of the sub:

whileprintingrecords;
shared numbervar sumofd;
shared numbervar tot := sumofd;

Then in the main report, you can add a formula like this in the
group footer:

whileprintingrecords;
shared numbervar tot;
distinctcount({table.date},{table.people})/tot

The other approach would be to create a SQL expression {%cntdt}like:

(
select count(distinct `date`)
from table A
where A.`people` = table.`people`
)

Syntax and punctuation depend upon your datasource and CR version.

Then you could use a formula like this for the total and then use it as the denominator:

sum({%cntdt})

-LB