Max of count

[Solojer]

Hello -- I'm copying the exact text from another post I found in the SQL forum as I'm wondering how to do this with Crystal XI. When I try to type (Maximum(count({Table.Field})), I get the message that this field cannot be summarized. All suggestions are welcome! Thanks

I have the following data:

Craftname day_of_week count
Craft 1 Monday 2
Craft 2 Monday 8
Craft 3 Monday 3
Craft 4 Monday 5
Craft 1 Tuesday 1
Craft 2 Tuesday 4
Craft 3 Tuesday 1
Craft 4 Tuesday 3


I would like to sum the above data by day then return the max count. For example

Monday = 2 + 8 + 3 + 5 = 18
Tuesday = 1 + 4 + 1 + 3 = 9

I need to return the number 18 or which ever day has the largest summed count. How can I do this in Crystal?

 

[IanWaterman]

You can add a group by Day of week,

@Day of week
weekdayname(datefield)

and add formula that uses a variable in footer to count total by day.


@DayCount
whileprintingrecords;

global numbervar daycount;

If count(countedfield, datefield, "daily") > daycount then weekdayname(datefield)

In report footer

@Display DayCount
whileprintingrecords;

global numbervar daycount;

Ian

 

[lbass]

If the numbers you are showing in your sample as "dayofweekcount" are from a database field and are not already a summary, you could do the following:

Group by day of week and then right click and insert a sum on the dayofweekcount field at the group level. Then go to report->group sort->choose sum of dayofweekcount as your group sort field->descending. You could either choose topN=1 to display only the maximum result.

If the dayofweekcount is actually a summary (a count) that you want to sum, then you could potentially create a SQL expression that would return the count per day of week that would allow you to implement my suggestion, since you could insert a sum on it at the group level. To help you with this, I'd have to know more about your database.

-LB