Solved

Percent Variance

Posted on 2008-10-09
7
602 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
ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

 
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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to get AD RMS to work with Office 2016 for Mac 6 267
Excel 2016 formulas 5 44
Finding a closest match in Excel 7 48
Word and Excel 2013 - Disabled Items List 3 63
Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

803 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