Crystal 8.5: Percent Difference from Previous Value 2

[ITSmith]

Hello,
Great forum - visited before, but new to submitting questions.

Maybe I missed them, but haven't seen docs anywhere on this.
We're trying to calculate the percent difference from a previous value in a column - in this case, an expired price.

The ends of the rows are like:

Beg. Date Expir. Date Rate/Unit % Change
07/01/04 02/02/05 0.350 ?
02/03/05 07/01/05 0.380 ?
" " 0.420 ?
etc.

All fields shown are direct from db - no formulas involved.

Is anyone aware of canned "running percent" calc's to
do this, or an alternate method ?

Thank You

 

[synapsevampire]

Use the previous() function, as in:

if not(isnull({table.field}))
and
{table.field} <> 0
and
not(onfirstrecord)
((previous({table.field})/{table.field})-1)*100

-k

 

[lbass]

I think that should be:

if onfirstrecord then 0 else
(previous({table.rate/unit})-{table.rate/unit}) % previous({table.rate/unit})

If you are calculating this within groups, you would have to change the formula somewhat.

-LB

 

[ITSmith]

Thank you, synapsevampire and lbass -
I'm employing something from each of your suggestions,
and the calcs are essentially working.

Been re-visiting this report again, and realized
I should have include a little more info. As lbass said,
some modifications are needed if there is
grouping.

No luck so far finding a function (or working out the logic) to re-start the calc on a change of record.
If you had any further thoughts, would be much appreciated -

Main groups are CustAbbr, ProdNo, and TermAbbr.
The desired calcs are in the details section, and records can change on CustAbbr, ProdNo or TermAbbr.

Ex:

Acme Prod Beg. Date Expir. Date Rate/Unit % Change

TermAbbr 'A'
100 07/01/04 02/02/05 0.350 ?
100 02/03/05 07/01/05 0.380 ?
" " 0.420 ?

Burns
TermAbbr 'A'
100 07/01/04 02/02/05 0.350 ?
100 02/03/05 07/01/05 0.380 ?
" " 0.420 ?

120 07/01/04 02/02/05 0.350 ?
120 02/03/05 07/01/05 0.380 ?

Continental
TermAbbr 'A'
120 07/01/04 02/02/05 0.350 ?
120 02/03/05 07/01/05 0.480 ?
" " 0.420 ?

TermAbbr 'B'
120 07/01/04 02/02/05 0.550 ?
120 02/03/05 07/01/05 0.680 ?
" " 0.420 ?

TermAbbr 'C'
120 07/01/04 02/02/05 0.650 ?
120 02/03/05 07/01/05 0.780 ?
" " 0.620 ?
Thank You Again -
ITS

 

[lbass]

Try something like:

if onfirstrecord or
{table.custabbr} <> previous({table.custabbr}) or
{table.prodno} <> previous({table.prodno}) or
{table.termabbr} <> previous({table.termabbr}) then 0 else
(previous({table.rate/unit})-{table.rate/unit}) % previous({table.rate/unit})

-LB

 

[ITSmith]

LB -

Your's and synapsevampire's suggestions are working well.
That last one did the trick for the groups -

Thanks again to both of you for the assistance.
Spot-checking the calcs looks OK.

The final version:

if
(onfirstrecord) or
{Table.Rate} = 0 or
(ISNULL({Table.Rate})) or
previous({Table.Rate}) = 0 or
{Table.Abbr} <> previous({Table.Abbr}) or
{Table.ProdNo} <> previous({Table.ProdNo}) or
{Table.TermAbbr} <> previous({Table.TermAbbr}) then 0

else

(({Table.Rate} - previous({Table.Rate})) / previous({Table.Rate})) * 100

Great info and suggestion in this forum !

Regards,
ITS