asked on

Crystal report loop formula?

I need a 'goal' line on a line graph that decreases by 3% each year (a moving target). A date in January of 2006 holds the baseline value of say...200. For each year after that, I need something like:

2006- 200
2007- 198.5
2008- 197.01
2009- 195.53
etc.

Is there a formula that I can create to make this line?

LinInDenver

Does your underlying data only contain one row per year?

Mike McCracken

Try this idea

Create a formula YearGoal

{StartingVlaueField} * exp(({Year({DateField}-Year({StartDateField})) * log(0.97))

That will give you the value desired for each year

You can then drop that formula on the chart as a MAXIMUM or MINIMUM value since all values are the same

mlmcc

Craig_Fr

ASKER

Thank you for the response! I'm making some progress but i'm not sure I plugged the values in properly. This is what I have:

{@baseline} * exp((Year({BILLING_MONTH}-Year(CDateTime (2006, 01, 01, 00, 00, 00))) * log(0.9925)))

{@baseline} is a constant number field of 200
{BILLING_MONTH} is a date field in the form of (2006, 01, 01, 00, 00, 00)
For the start date field, I put in the first date value and not a formula.

I get a constant line at the bottom of the graph when I put it in this way. When I use Month() instead of Year(), I can see a negative trend-line for each year (see attached (Orange line)). I admit I'm not familiar with exp() and log() formulas, so I greatly appreciate this!.
Experts-Exchange.jpg

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer