Varaiance formula question

[Abner2]

I am trying to do a report on percentage changes from month to month regarding crime rates. My report is grouped by month then by crime type:

June Count Percentage Change

Aggravated assaults 200
Burglary 439

July

Aggravated assaults 100 50%
Burglary 439 No Change

What is the formula I use to get the report to give me the percentage change from month to month automatically when I refresh the report.

Thanks

 

[dgillz]

Write a formula:

WhilePrintingRecords;
If OnFirstRecord then "No Change" else

If Not OnLastRecord the
totext({YourField}%Previous({YourField}),0)+"%"

else "No Change"

As you can see the first and last record cannot have a % change as there is no previosu or next value.

If these are group totals, replace {YourField} with your group summary operation. Software Sales, Training and Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[kenhamady]

I don't think that Previous() will work unless the value is in the prior detail - and this report is grouped.

If you are grouped by month and then by category you would be asking it to look back several groups for the previous value. This will require variables. Because you are grouped by Month first, you would need a separate variable, and several formulas to track each potential category. You would have to know the list of categories in advance.

It would be simpler if you could group by category and then by month. Then you could reuse the same variable and it would be more automatic. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[IdoMillet]

One possible solution is to use a subreport in each Month/Category group footer to return, via a shared variable, the value for that category (default subreport linking behavior) but for the previous month (using the Month as a second link but overriding the link behavior by specifying the month in the subreport must equal the month prior to the link value).

You would probably need to add the Year as a 3rd link value and, again, override the record selection logic (in case the Month is January, you would need the subreport to include December of the PRIOR year).

hth,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com