Bex limitation ?? Not able to get hold of Sum of a formula

[Former Member]

Hi Experts,

I have one issue for which it seems this is BEx limitation...?? plz prove me wrong because its sounds so simple that Bex should be able to handle it...

Heres the scenario :

I have 4 columns in the report ->

Demand : coming from cube

Forecast : coming from cube

Demand else forecast : formula ( if demand not equal to 0, take demand else take forecast.

Weight Factor : demand else forecast / SUMCT ( demand else forecast )

<b>Actual report :</b>

Demand, Forecast, Demand else forecast, Weight Factor

100 0 100 .5

0 50 50 .25

100 200 100 .5

0 0 0 0

-

---

200 250 200

-

---

<b> Correct/ Required Report :</b>

Demand, Forecast, Demand else forecast, Weight Factor

100 0 100 .4

0 50 50 .2

100 200 100 .4

0 0 0 0

-

---

200 250 250

-

---

Issue is with Result Row only of Demand Else Forecast...individual rows are coming correct..

Questions:

<b>1) Why result of demand else forecast is coming as 200 instaed of 250??</b>

((( I think becasue its applying formula to result rows also... so its picking up demand always...as some demand would be there...)

<b>2) How to get hold of correct sum of demand else forecast and thus weight factor would be correct...??</b>

(( Changing property of a Demand else forecast would NOT work as thats just for displaying purpose....even if I change its property it would display correct sum but would not use that figure in actual weight factor calculation - i have already tried out))

<b>3) How formula Collision works???</b>

(( Is it with just two structures?? how to utilize that in this scenario??... I have read SAP help but trying to use that here..but of no use..))

Plz help me - I dont want to tell users that I have to write VB code for this simple thing...

All help/ideas appreciated..

Thanks

Gaurav

Message was edited by:

Gaurav

Message was edited by:

Gaurav

[Former Member · Accepted solution]

Looks like You did every thing.

Are you displaying Result Row with that.

If it is Overall result ... try with SUMGT.

Nagesh Ganisetti.

[Former Member · Accepted solution]

Hi Nagesh,

I cant use SUMGT because i need to SUM on subtotals too....

<b>Any idea about <b>FORMULA COLLISION</b>, if it can be applied here?</b> What are pre-requistes of it?

Any ideas how to catch hold of SUM - its a pretty simple requirement.

Just IF/Else and then weighing...

Yes I hve tried SUM ( properties) in Result Row also for Demand else forecast..

Thanks

Gaurav

Message was edited by:

Gaurav

[Former Member]

Hi Gaurav,

I am not sure whether i understood your scenario properly or not.

I tried your scenario in my system. After creating the report, i made both calculated keyfigures property of the <i>Calculate Result As</i> as <i>Summation</i>. Now i am getting the result properly based on the values not formula. Like my total of the Demand Else Forecast is 250 and my Weight Factor is 1.25.

Is this solution is fine for us? If not let me know the exact situation of the Weight Factor calculation. Lets try.

Regards,

Vivek V

[Former Member]

Vivek,

Have you tried with figures which I listed above? If yes, how did you get right sum?? i m still wondering.

you need to take care that in some cases demand =0 and forecast NE 0 and in others Demand NE 0 and forecast =0..

Also I think theres one way to go :

In your demand else forecast : make it before aggregation, then I think it should work..

i cannot try this in my system because my frontend is not on BI 7.0 but back end is..!! and i cannt use BEfore Aggregation in my front end now....

Let me know how it works, if you can try this..

Thanks in advance.

Gaurav

[Former Member]

Hi,

Try to use Calculation Direction option. According to your issue, i gone throw help.sap.com. There they mentioned like this:

-

-

---

<b>Calculation direction</b>

Default calculation directions are not always as expected. You can change the calculation direction as required:

· Use the default direction (from top to bottom and from left to right)

· <b>Calculate along the rows (from top to bottom)</b>

· Calculate along the columns (from left to right)

The Calculate along the columns setting is useful, for instance, for all queries for which a time dimension is drilled down in the columns, and a cumulated output is to be created. This is shown in the following example query:

-

-

---

So for your calculated keyfigure, choose Calculate along the rows.

For your reference, refer this link:

http://help.sap.com/saphelp_nw04/helpdata/en/d2/02223c5f00612be10000000a11402f/content.htm

Regards,

Vivek V

[Former Member]

Hi Vivek,

Thanks for replying..

Cummulation alons rows/columns would be applicable ONLY if you are doing Cumulation - not when you are just doin formula..

Nevertheless, I made new column which is holding cumualtive values( remember we need to get hold of Sum).. and showed result as Last Value..But to my surprise I am not getting Result as last value ( which should be SUm of all values above in column "demand else forecast" which we need) but just the last vale which should have been there if there was no CUmulation...

<b>Seems Bex Properties are JUST FOR DISPLAYING..not for using them in further calculations...</b>..

Any IDeas experts....

Plz HELP......!!!!

Message was edited by:

Gaurav

[Former Member]

For basic keyfigure which are coming from the cube. you need to have results marked as summation.

Context menu and go to calculate result as "Summation"

For Calculated key figure mark results as the deafult one.

[Former Member]

Hi Meyyappan,

For basic KF, I believe t wont matter as SUM would always be correct - by settig default or Sum in properties....

Problem is with SUM of formula - i.e demand else forecast..

Hope I am clear..

Gaurav

[Former Member]

Sorry for formatting..i think it went wrong after the post

[Former Member]

Hi,

1) It's probably because the formula is also valid for the result row. And because the formula is <b>if demand not equal to 0, take demand else take forecast</b> and the result value is 200, that's probably why the value is 200. You could change that by changing the calculating keyfigure property Calculations->Calculate results as summation.

regards,

Raymond Baggen

Uphantis bv

[Former Member]

Hi Raymond,

Thats wht I think too...result row is taking formula into consideration..

I can DISPLAY sum by changing property but weight factor is not getting calculating based on "SUM" but rather on formula...

I have tried this and mentioned in above post....but it wont help with weight factor..

I need to caluclate weight factor correctly..