weird summary/graph problem 1

[bardley]

Hello.
Pretend for a moment that I have database records like this:

date-time stamp data value
3/12/2001 4:00:00 40.23
3/12/2001 5:00:00 50.89
. .
. .
3/31/2001 23:00:00 24.59

And what I wish to do is create a line graph that has:
on the horizontal axis, the hours in a week, starting with midnight on Sunday and continuing through 11pm on Saturday night.
on the vertical axis, the average of the data values for those specific values (in other words, I need to average the data values for Monday, 3/12/2001 8am, Monday, 3/19/2001 8am, and Monday 3/26/2001 8am together and graph that as a point).

I have been unable to do this so far, and I've tried all sorts of grouping scams. Please offer your advice!!!
Thanks.

 

[kenhamady]

The following formula will allow you to take a DateTime field and place it in one of the 168 hours in the week. You can then group on this formula, do your average by group and get your line chart using the average.

Problem #1, if any hours aren't represented in the data by at least one record, there won't be a value on the chart for that hour. The chart will skip it.

Problem #2
Printing the 168 values along the bottom of the chart is pretty tough. You might need to add your own approximate labels below the chart.

ToText(DayofWeek({@time}),0,'') +'. '+
WeekDayName(DayofWeek({@time})) +' - '
+Totext(Hour({@time}),0,'') Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[bardley]

Ken,
Thanks, it seems to work except for one little thing--the graph isn't what I had expected--it shows the normal periodic rise and fall of activity for each day in the week, but it appears to start in the middle of a day.
I am using select expert criteria that include data from only Monday through Friday, 7am to 6pm, and within a certain date range. So instead of 168 data points, I'm actually looking for 65. Is this affecting the formula? I don't see how it would, but obviously something is wrong because instead of looking like this:
/\/\/\/\/my graph looks like this:
'\/\/\/\/\,
Thanks in advance for your totally expert help!

 

[kenhamady]

The group data and averages should be visible on the report for comparison. That should help you figure out what is going on. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[bardley]

Oh, because now that we've concatenated numbers into a text field, we can't sort numerically anymore--it was sorting ASCII, in which 10 11 and 12 come before 7 8 and 9!

Thanks for your help Ken!