Skip to Content
0
Jun 05, 2006 at 06:51 PM

Calculating Result before aggregation in a formula

528 Views

Hi,

I have a formula in a query, in pseudo code:

if (col1 <> 0 ) then (col3 = col1 * col2) else col3 = 0. This works fine on an individual result basis. The issue is the total result lines (the rows are displayed as a hierarchy). I need the total result lines to display the sum of the individual results, NOT a calculation based on the total line values for col1 and col2.

I realize that this can be accomplished in a calculated key figure by setting the property "Time of Calculation" to "Before Aggregation". Unfortunatly, the selections and formulas behind col1 and col2 are so complexed, it would be impractical (if not impossible), to make them calculated keyfigures instead of formulas, and therefore I would be unable to make col3 a calculated keyfigure either.

Is there any way/trick/or workaround to have a query formula calculate before aggregation? Also, I am using a hierarchy display, so the properties "Calculate Result As" and "Calculate Single Value As" do not apply...

Any suggestions would be appreciated..