×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# trouble with complex or compound calculation

## trouble with complex or compound calculation

(OP)
I need to create a calculation using object A and object b.  Assume 10 rows of data.  the basic fomula is: [(A1/SUM(A1...A10))*B1] + [(A2/SUM(A1...A10))*B2] + [(A3/SUM(A1...A10))*B3] + ....[(A10/SUM(A1...A10))*B10] = answer.  Is there a way using SQL (or Business Objects) to create, a subquery maybe, that will be able to use (pass as a variable, possibly) the SUM of object a1 through a10 to another query?  Any suggestions are GREATLY appreciated!  Thanks in advance for any feedback.

### RE: trouble with complex or compound calculation

Rick, in your formula you are adding each of the terms together, so the total is equivalent to sum(a1*b1...a10*b10) / sum(a1..a10).
As long as you aren't concerned with the individual terms, you should just be able to have an object for a, and object for a*b, calculate the sums and divide one by the other.

### RE: trouble with complex or compound calculation

(OP)
Yaffle:  Thanks for the feedback.  Yes, your solution works but sometimes, but not always, users want to see the row-level detail in addition to the overall calculation.

This is the recommended solution I received from a Business Objects contact:

1. Create a SUM on the data in the column for object A.
2. Click on the cell containing the SUM and select Data > Define as Variable.
3. Select Evaluate the formula in its context and give it a name (eg Total Amount)
4. Add new column (object C) and use the formula:
Object A/Total Amount (of Object A column) * Object B
6. Format to 5 decimal places.

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!