obiee 12c calculated measure from two other measures - incorrect values

[Deleted User]

Missed your "complex agg" reply - yes that'd also be possible but it's a GUI setting you have to explicitly choose and if once forgotten or changed will again cause "wrong" results.

It's a "you have to die one death" type of situation, I agree.

Raj453

Hi Robert,

Tried changing the aggregation of measure C from AVG to SERVER COMPLEX AGGREGATE. It did not worked.

Below are the values i got for C.

$52.00    --- correct

$0.00      --- correct

$57.50    --- incorrect

$86.40    --- incorrect

$88.50   ---- incorrect

Please refer to the table of values in previous reply for what the CORRECT values are to be.  Thanks.

Robert Angel

Is this with the average logic in the formula?

avg( whatever you had before)

?

Raj453

The formula for C is A/B.

which is    SUM(MAX(fact column by dim1) BY dim2, dim3)/SUM(MAX(fact column by dim1, dim2, dim3) BY dim3, dim2)

To this before i applied AVG , now i changed to SEVER COMPLEX AGGREGATE.  This aggr rule is set in the "edit column formula" dialog box.

Robert Angel

Now you lost me, are you saying you want; -

A. SUM(MAX(fact column by dim1) BY dim2, dim3)/SUM(MAX(fact column by dim1, dim2, dim3) BY dim3, dim2)

or

B. avg(SUM(MAX(fact column by dim1) BY dim2, dim3)/SUM(MAX(fact column by dim1, dim2, dim3) BY dim3, dim2))

??

Raj453

I want B.

SonPat99

Dear User,

Please follow below steps:

1. Edit your column

2. Enter the formula as below:

avg(SUM(MAX(fact column by dim1) BY dim2, dim3)/SUM(MAX(fact column by dim1, dim2, dim3) BY dim3, dim2))

3. From the dropdown (where you selected AVG, please select Server Complex Aggregate) as shown below:

This is what Robert is trying to say.

Robert Angel

Wow!

Anyone with psychic powers please raise my hand?

Yes, thanks SonPat99 that is what I was suggesting, if that is what he wants...

Raj453

Hi All,

Got what i want. I created 2 more calculated measures in answers A1 and B1 before creating C.  Basically the 2 new measures are exact formulas of original A and B.

Then i created C as usual as A1/B1.   Then i set aggregation rule as SERVER COMPLEX AGGREGATE.  Then BOOM...   got the correct values.

I do not understand why i got wrong values when i initially created C using A and B.  (everything including data format are same for A and A1,  B and B1)

This i feel strange. Any insights gurus ?

Thanks again Rob, Chris and SonPat.

Raj453

Hi SonPat,

Got the expected values, but i did not use AVG as you shown in screen shot.  Just gave aggr rule as  Server Complex Aggregate.

Thanks.