Rolling total formula

[comdisco]

Can anyone tell me the syntax to calculate the last 12 months rolling total. The report parameters are period e.g. 01, 02, 03 etc and a year parameter. If the user selects period 06 in 2003 then I want the total to be for the last 12 periods e.g. period 06 - 12 in 2002 and periods 01 - 06 in 2003.

I'm a bit stuck on the syntax so any help greatly appreciated......

 

[comdisco]

Anyone please????????

 

[Madawc]

In Crystal 8.5, turn the values into a date, say by a formula field @Param_Date,
Datetime(Par_year, Par_month, 1)
Then use DateAdd to get @Start_Date
DateDiff("m", -12, @Param_Date)

To make comparisons, make another formula field, @After_Range, as
DateDiff("m", +1, @Param_Date)

The dates you want are equal to or greater than @Start_Date and before @After_Range.

Madawc Williams
East Anglia, Great Britain

 

[lbass]

I think Madawc meant to use dateadd in the formulas, not datediff. I would use the following for a select statement:

{@date} in dateadd("m", -11, {@parmdate}) to {@parmdate//where {@parmdate} = Date({?year},{?month}, 01) and {@date} = Date({table.year}, {table.month}, 01)

This would give you the period 7/2002 to 6/2003. For individual months within the period, you would use a formula like:

{@August2002}:
{@date} = dateadd("m",-10,{@parmdate})

-LB

 

[synapsevampire]

Are the periods stored as dates in the database?

If so then LB's solution should work, however make sure that it's passing the SQL to the database by selecting Database->Show SQL Query, it may not in this instance.

If your storing the periods as months, use a similar formula to determine if the year rolls back.

-k

 

[comdisco]

Thanks for all your suggestions. I have followed Madawc's advice but when I run the report I am getting Bad Date format string on the Param_Date formula field.

Is it because the period field that I am trying to set to Date is held as a string in the database???

 

[Madawc]

To convert a string into a date, use CDate (string)

Madawc Williams
East Anglia, Great Britain