Percent Variance

Posted on 2008-10-09
Last Modified: 2011-10-19
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!!
Question by:rocketmonkey
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 16

Expert Comment

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.
LVL 100

Expert Comment

ID: 22680961
How are you getting the values for each month.

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


Author Comment

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.

Create Professional Looking Email Signatures

Create "Professional HTML Email Signatures" with ease.
7 Day Money Back Guarantee if not 100% Satisfied.
Affordable - Try it out for 7 Days Totally Risk Free.
Installers provided for over 45 Email clients.
Both Windows & MAC Supported.
Highly Recommended!

LVL 100

Accepted Solution

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
Global NumberVar Year1Month1;
Global NumberVar Year2Month1;
Year1Month1 := -1;
Year2Month1 := -1;

In the group 4 footer

Global NumberVar Year1Month1;
Global NumberVar Year2Month1;
If Year1Month1 < 0 then
    Year1Month1 := {Your Summary Field}
    Year2Month1 := {Your Summary Field}

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


LVL 100

Expert Comment

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


Author Closing Comment

ID: 31504756

Featured Post

Office 365 Training for Admins

Learn how to provision tenants, synchronize on-premise Active Directory, and implement Single Sign-On with these master level course.  Only from Platform Scholar

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Calculating Sales Tax 13 66
Stop macro from going to a different sheet in Excel 18 48
Excel not storing numeric string correctly 5 38
Office 365 Spam 3 30
Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
This article will show you how to use shortcut menus in the Access run-time environment.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

737 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