trouble with complex or compound calculation 1

[Rick0124]

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.

 

[yaffle]

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).<br>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.

 

[Rick0124]

Yaffle:&nbsp;&nbsp;Thanks for the feedback.&nbsp;&nbsp;Yes, your solution works but sometimes, but not always, users want to see the row-level detail in addition to the overall calculation.<br><br>This is the recommended solution I received from a Business Objects contact:<br><br>1. Create a SUM on the data in the column for object A.<br>2. Click on the cell containing the SUM and select Data &gt; Define as Variable.<br>3. Select Evaluate the formula in its context and give it a name (eg Total Amount)<br>4. Add new column (object C) and use the formula:<br>Object A/Total Amount (of Object A column) * Object B<br>6. Format to 5 decimal places.