Craig_Fr

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?

2006- 200

2007- 198.5

2008- 197.01

2009- 195.53

etc.

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

Does your underlying data only contain one row per year?

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

Create a formula YearGoal

{StartingVlaueField} * exp(({Year({DateField}-Yea

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

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

{@baseline} * exp((Year({BILLING_MONTH}-

{@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

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds.

**No credit card required.**