Solved

Percent Variance

Posted on 2008-10-09
7
599 Views
Last Modified: 2011-10-19
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
Comment
Question by:rocketmonkey
  • 3
  • 2
7 Comments
 
LVL 16

Expert Comment

by:wykabryan
ID: 22680859
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 22680961
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 Comment

by:rocketmonkey
ID: 22681141
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 22681295
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 22683561
If you need the grphic deleted I can get that done for you.

mlmcc
0
 

Author Closing Comment

by:rocketmonkey
ID: 31504756
Thanks!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now