asked on

Percent Variance

Hi,
I have to calculate the percent variance in a Crystal XI report from for each month by Category. Below I am trying to calulate the Month1Variance, Month2Variance...etc. This must be done in Crystal and not SQL, so it will have to be a Crystal function. Also attached is a file.

Category 1
Year 1 Month1 Month2 Month3
Year 2 Month1 Month2 Month3
Month1Variang Month2Variance Month3Variance
Category 2
Year 1 Month1 Month2 Month3
Year 2 Month1 Month2 Month3
Month1Variang Month2Variance Month3Variance

Thanks in Advance!!

wykabryan

You almost want to put this into a cross tab. It would allow you to calcuate the the percentage. This way you are going to struggle a lot because you are grouping on year. The numbers associated with the grouping are summaries based around that grouping; which is next to impossible to figure out in the current lay out.

A cross tab you could set up just like above except you would have the power to put the percentage in.

Mike McCracken

How are you getting the values for each month.

You will need 12 formulas. One for each month to calculate the vairance.

mlmcc

rocketmonkey

ASKER

Thanks guys.

Currently, the months are are a group summary, so that there is the ability to drill into the details.

I understand that i would have to have a formula for each month, but I am not sure what that formula would be since it is based upon the previous record (previous year) by category by month.

Attached is the design view.

thanks!!
PercentVariance-DesignView.jpg

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike McCracken

If you need the grphic deleted I can get that done for you.

mlmcc

rocketmonkey

ASKER

Thanks!!