3 month average for 9 month period

[jschill2628]

How do I In a cross tab get the last three month worh of data to average for a 9 month period.

I have a report that spits out data for a rolling 9 months, currently I dump it into excel and continue on my way, but I would like to schedule the report to automate for me in order to do this, I need to figure out the 3 month average, and the percent in which it is above or below the baseline.

For example:
Baseline: 17
3/2011: 23
4/2011: 17
5/2011: 12
6/2011: 24
7/2011: 16
8/2011: 8
9/2011: 12
10/2011: 13
11/2011: 14
3 month avg: 13
3 month average above or below baseline: -21%

I need to put all the above information into a cross tab, is there a way that crystal figure out the 3 month avg, and the percent above or below the baseline, and pit it into a cross tab?

 

[lbass]

This isn't really clear. Are you saying you just want the average of the last three months of data (in this case, sep,oct,nov)?

In the crosstab expert, what are the row fields, column fields, summary fields and types of summaries?

-LB

 

[RTag]

Are you trying to calculate the average per quarter ?
If yes, then add additional column for the quarter and use it in the crosstab. This will give the average for each quarter:
3/2011:Q1, 23
4/2011:Q2, 17
5/2011:Q2, 12
6/2011:Q2, 24
7/2011:Q3, 16
8/2011:Q3, 8
9/2011:Q3, 12
10/2011:Q3, 13
11/2011:Q4, 14

If you want to get the last 3 months (i.e. 9,10 and 11). Create a column to identify them:
3/2011:Regular, 23
4/2011:Regular, 17
5/2011:Regular, 12
6/2011:Regular, 24
7/2011:Regular, 16
8/2011:Regular, 8
9/2011:Last3Months, 12
10/2011:Last3Months, 13
11/2011:Last3Months, 14

and group by this column. Date column will be not used in the crosstab.

BTW, If your goal is to export the data to excel you can do all this faster and easier directly in your database and save it as excel file without creating crystal report.

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[jschill2628]

I do not want to report in excel, it is currently in excel, and I am trying to create it in crystal, so I can automate it, and burst it out.

I realize I probably cannot put this field/formula into the same cross tab, which is okay. What I want is just want the average of the last three months of data (in this case, sep,oct,nov)? a.k.a. the number 13 in this example.

This report is a rolling 9 month report, so when I set it up to run my selection criteria is as follows:
{IRSCLAIM.CREATE_DT} in dateserial(year(currentdate),month(currentdate)-9, 1) to
date(year(currentdate),month(currentdate),1)-1


Is there a formula I can write that will pull out the last three months worth or data, and give me the average?

Thanks in advance!

 

[RTag]

Create parameter @Months and modify your formula to get the last @Months of data:

{IRSCLAIM.CREATE_DT} in dateserial(year(currentdate),month(currentdate)-{@Months}, 1) to
date(year(currentdate),month(currentdate),1)-1

http://www.R-Tag.com

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

 

[jschill2628]

That is not what I need. I need a formula that will give me the average number of incidents over the past three month. I don't want any parameters, and the formula you gave me, does not work, becuase it's trying to reference itself.