Calculation before Aggregation (BI 7.0)

[former_member463021]

Hello,

We have a datastore with 2 characteristics (C1, C2) and 1 key figure (R1) with those values:

C1     C2     R1

--------------------

A1     B1     -1

A1     B2     -2

A1     B3     4

We've created 2 calculated key figure R2  (show negative numbers) and R3 (show positive numbers):

C1     C2     R1     R2     R3

------------------------------------

A1     B1     -1     -1    

A1     B2     -2     -2

A1     B3     4                    4

A report with characteristic C1 and R1, R2,R3 aggregated after calculation:

C1     R1     R2     R3

----------------------------

A1      1                    1                     

And we'd get the next report (before after agregation):

C1     R1     R2     R3

----------------------------

A1     1         -3        4

Thanks.  

[Loed · Accepted solution]

Hi Pedro,

How did you compute this?

C1     R1     R2     R3

--------------------------------

A1      1                 1  

Also, what output do you want to get as the final result?

Regards,

Loed

[former_member463021 · Accepted solution]

Hello,

I compute that result in Bex Analyzer; double click in characteristic C2, it agregates at C1 level.

C1 could have different values, and the report doesn't show overal result.

The final result would be:

C1     R1     R2     R3

-----------------------------

1        1          -3     4

First, it calculate key figure (R2,R3) and after aggregate; the same like it does in 3.0 version; it allows calculated key figure and aggregate after calculation.

Thanks

[Loed · Accepted solution]

Hi Pedro,

In BEx Analyzer, I think removing C2 will give your desired output since it will just sum the values in each column..No need for before or after aggregation thing..Did you try it?

Regards,

Loed

[former_member463021 · Accepted solution]

Hello,

Output sum values in each column if the key figures are loaded in datastore, the problem is with calculated key figure R2,R3 that I've created in query designer.

BW 3.0 version allows calculated values before agregation  in our calculated key figures, but BI 7.0 doesnt; so, first it agregates:

C1 R1 R2 R3

-------------------

A     1

And after it does calculations.

C1 R1 R2 R3

------------------

A     1           1

I've definened in query designer:

                          R2 = (R1 < 0) * R1

                          R3 = (R1 >= 0) * R1

Thanks

[Loed · Accepted solution]

Hi Pedro,

In Exception Aggregation, try ticking the TOTAL and Ref.Char to C2..

If the method above did not work, create a GLOBAL CKF using the same formula of R2 and R3..In the exception aggregation tab, just choose TOTAL and untick the CALCULATE AFTER AGGREGATION so that it will sum BEFORE AGGREGATION..

Regards,

Loed