×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Students Click Here

trouble with complex or compound calculation

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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close