aggregated result as key figure

[Former Member]

Hello Experts,

I hava to design a bex query for which i need to write an if condition.

In that If condition the metric(logical value) is sum of all the values assosiated with drill down.

For example: i have a query with 10 companies as rows and their units for different months as colums, now i need to create a formulae(if condition) where i need aggreated value of all the shipments for specific month associated with all companies(overall result). How can i include the overall result value in formulae as it isnt a specific keyfigure/selection.

company Units 2007 Units 2008 Units 2009 Units 2010

xxx 1 2 3 4

yyy 5 6 7 8

zzz 9 10 11 12

Overall resul P Q R S

I need to write condition on the overall result(p,q,r,s) which actually came from bex(not specific key figures).

please help me in this issue, i was almost loosing my hair with this issue.

Thanks

Prashanth

[Former Member]

Hi Karthik,

If the number of companies and number of months used for reporting are fixed, Then you can do this using cell definition concept. The process would be:-

Say if you are interested in only 10 companies and 4 months, then you need to define a matrix like format using two structures.

one structure will contain 10 rows each restricted to a specific company, another structure will have 4 columns each restricted to a month. This will give a total of 40 cells. and an additional 4 RESULT cells. Now you can define you if condition to work as you want on these 4 result cells using cell definitions.

Note:- this is possible only when the number of companies and months taht you are interested in are fixed.

Hope this helps you.

Thanks & Regards,

Nithin Reddy.

[Former Member]

company Units 2007 Units 2008 Units 2009 Units 2010

xxx 1 2 3 4

yyy 5 6 7 8

zzz 9 10 11 12

Overall resul P Q R S

I need to write condition on the overall result(p,q,r,s) which actually came from bex(not specific key figures).

please help me in this issue, i was almost loosing my hair with this issue.

Thanks

Prashanth

[Former Member]

I dont think there is a way of doing further calculations on results.

Perhaps you can either:

1. do the inital calculation in a formula or Calculated Key Figure, and then use that to perform further calculations (reccomended)

2. or, do the inital calculation in the original (or new) data provider and then perform further calculations on that (more work)

3. or else, create an intergrated planning datastore to write the results back to BI, and perform the calculations on that (hard, not reccomended)

Good luck,

Ruben

[Former Member]

Hi Karthik,

You cant use the overall result for your calculation in Bex. Instead you can use a calculated KF in your Bex which does the same calculation as your Overall result and then use it for further calculations.

Hope it is clear!!

Regards

Sunil

[Former Member]

sunil,

How could i define a CKF/Formulae which calculates the reults in column wise( for specific key figure)...