Moving Averages 1

[triceratops]

Can anyone provide a method to create a moving average?

I want to graph a moving average of data against time. My records are sorted in chronological order. I want a moving average of the number, basically a count of the records in a two-week time period.

I'm using CR version 7 connecting to a Remedy date on an Oracle database using ODBC.

Thanks.

 

[kenhamady]

Each average includes 2 weeks, but how often does the average change, daily?
How long of a time period will the entire report be for? Ken Hamady

http://www.kenhamady.com/

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

 

[triceratops]

I'm not interested in how often the data changes daily since I'll only be reporting by weeks anyway. So that could default to any convenient method.

My ACTUAL data varies daily, if that is what you are asking. It varies quite a bit, probably in the range of 3-15 per day. I'd say typically its on the low end, 3-5 per day.

The report will cover a full year from the current date.

This isn't an urgent problem, but I've been working on it off and on for over a month now and its getting under my skin.

 

[kenhamady]

This may be too complex for me to handle within the forum. Part of the problem is that your description doesn't give me a clear picture of your goal. In other words this might require a consulting project, but I will give it a shot:

Do you really want a moving count or a moving average?
If average, what numbers would be used to calc the average (what divided by what)?
Can you give an example of what the first part of the output might look like for a sample year? Ken Hamady

http://www.kenhamady.com/

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

 

[triceratops]

A moving count is more accurate. I think of this data in terms of daily numbers, so I think of the report as a moving average of that number. But a count is more correct.

I want to print a graph of the number of entries we get into/close out of Remedy. I want to smoothe out the graph, which would be a scattered collection of points on the timeline. Our work is in cycles, lots of input (to Remedy) followed by work and output. I want to try to characterize the lag time or phase delay in these cycles, as well as identify any compression in the cycles themselves.

I was trying to avoid saying that because I wasn't sure I could say it clearly.

-Randy King

 

[kenhamady]

I will take a stab in the dark here on what the chart will look like for one year:

A stacked bar chart with 26 bars, one for each 2-week period. 2 segments to each Bar, Opened in that 2-week period and closed in that period. Is that what close?
Ken Hamady

http://www.kenhamady.com/

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

 

[triceratops]

Oh, you mean that detail. Sorry, I completely missed where you were going.

That would work acceptable. A better option would be a bar for each week showing the previous two weeks. My ideal chart would be a line chart that shows a moving count for the two weeks prior to each date.

 

[kenhamady]

This can only be done roughly in V7 and only as a bar chart. It doesn't seem to work with lines. It requires a different technique and a bit more work than what I suggested. It also doesn't allow you to label the bars.

The key difference is that now you need each record to count in two different groups totals. That takes you beyond simple grouping and forces you to create a conditional grand total for every bar. Here is an outline of the process if you want to try it:

1) You will need start date and end date parameters.

2) you will need 52 conditonal formulas that have the following structure:
If {datefield} in {?startdate} + 7 to {?startdate} + 21
then 1 else 0

One of these for each week, with the numbers changed appropriately.
Next, you need a grand total that sums each of these formulas.

Last you add a bar chart using the "Detail" option on the data tab. Put all of the totals into the "Show values" area. In the "On Change Of" you will need a field that is a constant value for the whole report. You probably want to shut off the legend.

Ken Hamady

http://www.kenhamady.com/

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