?
Solved

Cross Tab-Need to Exclude Zero Values from Average Formula

Posted on 2011-03-08
7
Medium Priority
?
1,597 Views
Last Modified: 2012-06-27
Trying to calculate Average of Annualized Salary Change amounts but it is off because zero amounts are being calculated in the average.  Any help would be most appreciated.
0
Comment
Question by:twkyoscr
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 19

Expert Comment

by:GJParker
ID: 35070899
Show details of your crosstab setup and the calculations your using.
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35071216
The only way to affect summaries in the cross tab is to not include the 0 data in the report.  I assume th e0s are because of lack of data for a given row or column.

mlmcc
0
 

Author Comment

by:twkyoscr
ID: 35071455
Mlmcc,
The problem that I am having with the cross tab is that I cannot seem to keep a placeholder for null values.  What I ended up doing is writing a union (in Peoplesoft Query) to also inlcude those that had no adjustment so that the report would still have each manager listed.  I excluded those that had no adjustment from the counts, but the average is now the issue.  I created a running total which I entered a formula under evaluate to do the following: {@Annualized ChgAmtRnd}<>0
However the average amount is correct for some managers and off slightly for others.  
crystal-issue.bmp
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 400 total points
ID: 35072476
NULL is not the same as 0 unless you are converting it.

The cross tab uses the information presented so if there is no data it converts that to 0 and then unfortunately uses it in the summary.

The only way I can see around this is to build your own manual cross tab and calculate the average s without including the NULLs

mlmcc
0
 
LVL 35

Accepted Solution

by:
James0628 earned 1600 total points
ID: 35106631
You might be able to do this using a weighted average, instead of a regular average.  If you don't want the average to include the records where {@Annualized ChgAmtRnd} = 0, you could create a formula like this:

if {@Annualized ChgAmtRnd} <> 0 then
  1

 Then make your summary "Weighted average with", and select that formula for the "with" part.  The idea is that all of the records where {@Annualized ChgAmtRnd} is not 0 will be given a weight of 1, so they'll be included in the average (with an equal weight), and the records where {@Annualized ChgAmtRnd} is 0 will have a weight of 0, so they'll be excluded from the average.

 This could work.  It depends a lot of what's in the {@Annualized ChgAmtRnd} formula.

 James
0
 

Author Closing Comment

by:twkyoscr
ID: 35217402
Thanks everyone for your assistance!  After applying your suggestions and tweaking the data a bit, I ended up with the correct results!
0
 
LVL 35

Expert Comment

by:James0628
ID: 35221514
You're welcome.  Glad I could help.

 James
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

571 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