• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 635
  • Last Modified:

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!!
0
rocketmonkey
Asked:
rocketmonkey
  • 3
  • 2
1 Solution
 
wykabryanCommented:
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
 
mlmccCommented:
How are you getting the values for each month.

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

mlmcc
0
 
rocketmonkeyAuthor 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

I assume the total cannot be negative.

Group 3 header
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
    Year1Month1 := {Your Summary Field}
else
    Year2Month1 := {Your Summary Field}

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

mlmcc

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

mlmcc
0
 
rocketmonkeyAuthor Commented:
Thanks!!
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now