Weekly Average of Daily Max

[AvgMoJoe]

Using Crystal 2008

I have a field {Level} which contains a measurement between 0 and 100 and can be recorded multiple times during a day.
Group 1 is by week (using a formula {@WeekBeginning} which accounts for starting on Saturday rather than Sunday).
Group 2 is by day (using the 'for each day' common group option.)
I simply use the Max summary for the Daily Max, but I would like to get an average of the daily Max for each week. Could you suggest formula(s) I should use to accomplish this?

Week Beginning 5/19/2012 Average of Daily Max: (20+50+30+...+70)/7
----------------------------------------
5/20 Daily Max: 20
[tab]5/19 20
5/20 Daily Max: 50
[tab]5/20 50
[tab]5/20 40
5/21 Daily Max: 30
[tab]5/21 20
[tab]5/21 10
[tab]5/21 30
....
....
5/25 Daily Max: 70
[tab]5/25 70
[tab]5/25 40

Week Beginning 5/26/2012 Average of Daily Max: ...
----------------------------------------

 

[MCuthill]

AvgMoJoe,
I beleive a running total of sorts should be able to handle your request. To complete, you will need three formula fields (as follows).

{@VariableReset_WkAvgDailyMax} <<< place this formula in GH #1 (group header for week)

WhilePrintingRecords;
Shared NumberVar WkAvgDailyMax:=0;

-- This formula field "resets" the running total to zero at the start of a new Week grouping.

{@VariableAccum_WkAvgDailyMax} <<< place this formula in the group header (or footer?) where your Maximum() calculation takes place.

WhilePrintingRecords;
Shared NumberVar WkAvgDailyMax;
WkAvgDailyMax:= WkAvgDailyMax + Maximum({YourField},{Group #2 Field});

-- This formula will add the Maximum Value for a given day and add it to the running total.

{@VariableDisplay_WkAvgDailyMax} <<< place in GF #1 (group footer for Week)

WhilePrintingRecords;
Shared NumberVar WkAvgDailyMax;
WkAvgDailyMax:=WkAvgDailyMax/7;

-- This formula will take the total of the Maximum Values and divide by 7. This approach assumes that values will ALWAYS exist for all 7 days of the week. If this is not an accurate reflection of your data, please advise and I can update the above to calculate an average based on the number of days reported in a week.

Cheers AvgMoJoe, hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[AvgMoJoe]

Beautiful! Thank you kindly sir! I appreciate you taking the time to swiftly answer this!
If I were to account for days without data, I'd just add a shared variable for a count to divide by, with a little div by 0 check correct?

 

[MCuthill]

Your assumption is spot on, AvgJoMoe!

A variable that increases with each day found and divide by, or perhaps simply a distinct count of Days within the group to divide by.

Glad I was able to help with your question. Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[AvgMoJoe]

I have an addendum.

How can I chart these result (Weekly Averages of Daily Max)? Of course my only choices using the GUI are any summary fields I added to the Weekly Group.

 

[MCuthill]

Good Morning AvgMoJoe,

I am not aware of a means of using formula's like the above to generate charts, I know it isn't as automated a choice as the Crystal Charting, but you could design your report to be an export file which can be copied into an Excel Template that generates the charting.

That being said, perhaps another Tek-Tipper knows a way to use such formula's in charts and can assist. In the past, I have always used the MS Excel approach for Charts on reports such as this.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."