# 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

###### Who is Participating?

Commented:
Try this for one month.  You can duplicate for the others.

I assume the total cannot be negative.

WhilePrintingRecords;
Global NumberVar Year1Month1;
Global NumberVar Year2Month1;
Year1Month1 := -1;
Year2Month1 := -1;
''

In the group 4 footer

WhilePrintingRecords;
Global NumberVar Year1Month1;
Global NumberVar Year2Month1;
If Year1Month1 < 0 then
else

In the group 3 footer
WhilePrintingRecords;
Global NumberVar Year1Month1;
Global NumberVar Year2Month1;
(Year1Month1 -Year2Month1) / Year1Month1 * 100

mlmcc

0

Commented:
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.
0

Commented:
How are you getting the values for each month.

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

mlmcc
0

Author Commented:
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
0

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

mlmcc
0

Author Commented:
Thanks!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.