counting and averaging based on days of the week and months

[grouchal]

I have a set of data that is in the format of data/time this data relaets to a set of activities that have happened.

I need a report that tells me the maximum number of times this has happened per day of the week and the average number of time per day of the week.

I seem to be having great difficulty in doing this because I first need to generate the number of events per date - can do this and get a count. Then I need to group by day of the week - this does not seem to be a simple task, and then I need to average over a set of counts and find a maximum from a set of counts!

Help this sounded like the sort of thing I should be able to do easily with a reporting tool but am getting very confused!

 

[Naith]

Grouchal, from the initial description, this is what I envisage your report being made up of:

Your grouping field (@Formula1)
DayOfWeek({YourDateFieldHere})

Your incident count by day (@Formula2)
Count({YourIncidentIndicatorHere},{@Formula1})

Your maximum formula (@Formula3)
WhilePrintingRecords;
NumberVar Max;

If OnFirstRecord
Then Max := Count ({YourIncidentHere}, {@Formula1})
Else
If Count({YourIncidentHere}, {@Formula1}) > Max
Then Max := Count ({YourIncidentHere}, {@Formula1})
Else Max := Max;

Your average formula (Formula4)
Count({YourIncidentHere}) / 7

I doubt that this gives you a comprehensive selection of everything you need, as there are some grey areas in your intial post: I'm not sure whether you mean date by day when you say day of the week, for example, and you make reference to month, but it isn't clear how this is integrated into the report.

If the above leaves some gaps in what you want, try posting some examples of where you hope to be.

Naith

 

[synapsevampire]

This is Crystal version dependent, please share your environment when posting.

Insert the date field and Group by the date, and change the group by to for each week

Insert the date field again and Group by the date, and change the group by to for each day

Place something like Naith's formula in the Day Group, as in:

Your maximum formula (@Formula3)
WhilePrintingRecords;
NumberVar Max;

If OnFirstRecord
Then Max := Count ({YourIncidentHere}, {@Formula1})
Else
If Count({YourIncidentHere}, {@Formula1}) > Max
Then Max := Count ({YourIncidentHere}, {@Formula1})
Else Max := Max;

Now in the Week Group you can use the following in a formula:

WhilePrintingRecords;
NumberVar Max

Hopefully this will get you through it.

-k

 

[kskid]

For average per day of week, I used the following set of formulas.

1. I Inserted a group on @formula1

2. I inserted a distinct count of date within day of week

DistinctCount ({YourDateField}, {@formula1})

3. Instead of @formula2, I inserted a count of {YourIncidentHere}

Group1: Count ({YourIncidentHere}, {@formula1})

4. Finally the average formula

if Count ({MINC.GRAM_NO}, {@Day}) = 0 or DistinctCount ({MINC.CREATE_DT}, {@Day}) = 0 then
0
else
Count ({YourIncidentHere}, {@formula1})/DistinctCount ({YourDatefield}, {@formula1})

For example, If the numerator or denominator is 0 then 0 else, divide the number of incidents I found for Tuesday by the number of unique Tuesday dates I found.

This way it does not matter how many days you select in your range to get an average per day of week