Running average

[triceratops]

I am trying to calculate a running average over time for a graph. I have subreports that collect the data in the date range, get a count and successfully pass the count back to the main report. The count is passed into a parameter field. That works fine.
Now when I try to do a line graph, all I can get is a count of the parameter field rather than its value.

How can I graph the value, or is there a simpler way to calculate a running average?

Thanks.

 

[kenhamady]

Please see the FAQ on what to include with your question. Also explain what the count has to do with the running average, and how you plan on graphing a single value. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[triceratops]

We're using CR version 7.0.1.100 connecting throu ODBC.

The count reference probably falls into the too much information category, it is probably irrelevant. However, to calculate the running average I am generating a subreport between two dates and counting the entries. This value is passed back to the main report for graphing.

I (seem to) have to use subreports since the dates overlap, it seems inherent in the design of CR that data is processed linearly. If that understanding is incorrect, my task could probably be done in a simpler manner.

My intention is to graph the count against time, actually the upper bound of my time range used in the subreport. Using a linear graph, I'll get the running average against time.

I hope that is clearer.

-Randy

 

[kenhamady]

It sounds like you are trying to calculate a series of numbers (one per subreport) and then graph the series in the main report.

I am confused because you said you pass the value into a parameter field of the main report. I don't think that is correct.
Parameter fields are user prompts. They can link a Main to a SubReport but not the other way.

You may not need subreports, if you explain the data to me I might be able to simplify.

Ken Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[triceratops]

Oops, you are correct. It isn't a parameter field, it is a formula field. Sorry for that misinformation.

I'm just trying to calculate the number of records between two dates. The problem I had in not using a subreport, was that the dates overlap. I couldn't find a way to get the record count when a record belonged to more than one date range.

I am using groups for the date range, or more accurately, the group is the last valid date in the range. Currently, I am using a five-week range and in week increments. So each record belongs to five groups. I am keeping the range variable, so I can play with its value once the report works.

The graph will be the number of records in each group. So it is the number of records in each of the five-week periods.

I need to use the running average (or the five-week period) because the data is rather sporadic. Perhaps sporadic isnt' the word I want. The data is irregular, it comes in spurts. I want to smooth it out to get a curve that we can draw inferences from.

Thanks,
Randy

 

[kenhamady]

Please describe the average in more detail. I think you really want a "moving" average if you are trying to smooth out a curve. A "running" average would be cumulative from the beginning, while a moving average would only include the past x values.

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[triceratops]

You are correct, I was getting my terms confused. It is a moving average.

I'm not sure what more there is to tell. I started to write something more, but it came out to pretty much what I wrote the last time.

Essentially:
I am plotting the number of records submitted over a five-week period and dividing by 5 to get the moving average for a week. This is plotted for each week.

 

[kenhamady]

I don't think you can create a chart using values collected from subreports.

This is because you need shared variables to get the value from a subreport. These have to be whilePrintingRecords and you can't create a chart of values that are calculated while printing.

There may be a way of doing moving averages within a single report, although I am not sure that it would be possible to chart them.

It would take more time than I can devote to free TT responses. You can contact me directly if you want a consultant. Or you can try posting a new clearer question like:

Charting Moving Averages:

Has anyone been able to chart a moving average using CR? I need a 5 week moving average of a weekly total in a user defined time period. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[dcm]

Triceratops: If I've understood what you are trying to chart this sounds very much to me like the S Curve chart which would plot the number of hits in a running total (or average) over time. The issue here is that you cannot chart on anything which is not available in advance of report production (i.e. the WhilePrinting state). However, what we have done in the past is to create one report which calculates the count of entries for each date (e.g. a summary field) and then exports the result e.g. to a CSV file. This file is then used as the basis of either the main or subreport in a subsequent report which charts the accumulating running totals. (Incidentally you can pass values through the use of shared variables in both directions!)

Hope this helps, if you need any more just ask

David Monks

http://www.chase-international.com

Accredited Seagate Enterprise Partner

 

[triceratops]

Thanks guys.

I appreciate the help you've given. I'll try reposting as Ken suggested. David's idea seems more complex but looks promising too.

-Randy