Rolling 12 month average

[Simon9999]

Hi

I am using crystal xi and am struggling to calculate a rolling 12 month average - I have searched the site but have not found anything that quite answers my question.

I am trying to chart the average number of bids submitted in a 12 month period compared to previous periods. For example when I run the report in January 08 I want to see:

Dec 2007 - 45 (i.e. 45 bids submitted 1/1/07 - 31/12/7)
Nov 2007 - 43 (i.e. 43 bids submitted 1/12/06 - 30/11/7)
Oct 2007 - 46 (i.e. 46 bids submitted 1/11/06 - 31/10/7)
Sept 2007 - 40 (i.e. 40 bids submitted 1/10/06 - 30/09/7)

etc. etc. for last 12 months.

I am having difficulty finding a way of calculating this in a way it can be charted.

My current plan is to create a formula for each month i.e. month 12, month 11 etc. and then creating a further formula that sums each formula depending on the current month, but this involves a lot of work where the dates cross year ends.

Any help would be much appreciated

Thanks

Simon

 

[Madawc]

You can get data for the last 12 months using DateDiff. Then group by month. Depending on your version (which you need to give) you might be able to group directly by month on a date. Failing that there is DatePart and also Year and Month.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

You could set up a series of conditional formulas like this:

//{@lastmo}:
if {table.date} in dateserial(year(currentdate), month(currentdate)-12, 1) to dateserial(year(currentdate), month(currentdate), 1)-1 then {table.amt}

//{@twomosago}:
if {table.date} in dateserial(year(currentdate), month(currentdate)-13, 1) to dateserial(year(currentdate), month(currentdate)-1, 1)-1 then {table.amt}

//{@threemosago}:
if {table.date} in dateserial(year(currentdate), month(currentdate)-14, 1) to dateserial(year(currentdate), month(currentdate)-2, 1)-1 then {table.amt}

//etc.

Insert a chart and choose "For all records" and then add the sum of each formula as your "show value" fields.

-LB

 

[lbass]

Sorry, I didn't notice the 'average'--choose average as the summary for each formula.

-LB

 

[Simon9999]

Thanks for your help. I have used similar formulas to the above to count the number of bids submitted.

However, I need to display the data as a line graph over month headings if possible e.g.

Jan07 Feb 07 Mar 07 Apr 07
40 45 42 40

Do you know if this is possible?

 

[Madawc]

If you group by months, you'll also be able to produce a graph of these values.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10