Creating a Formula on an Exception aggregation Total Column

[Former Member]

Hi SDN Community,

I have the following scenario

Column A Column B Column C

value1 1 value1 divided by total of column B

value2 1 value2 divided by total of column B

value3 1 value3 divided by total of column B

Totals

Value123 3

I have created a CKF with only th value 1 in it and put in the column B, and placed after Aggregation on the properties of the CKF to derive the total result for column B successfully

I now want to create in Column C a formula which divides the value by the result of the column B.

I have tried using the formula

Column A / SUMCT Column B

where the SUMCT Column B value should be = 3

Does anyone have any ideas for the above.

Thank you.

Simon

[Former Member]

Simon,

I think you will have to look at the Define Cells option.

I can provide info but there are many posts on Define Cells so it may be better to have a search for these rather than repeating post contents.

regards

Gill

[Former Member]

Hi Gill, Aparna, Muralidhar

Thank you all for your replies.

Unfortunately, because one of the characteristics feeds off a hierarchy, which is dynamic, we firstly cannot use structure elements.

I will shortly be trying the SUM formulas you have suggested, but have a feeling that with my first initial attempts it will not work.

I will demonstrate with values the table:

Equipment Threshold CKF Counter

DRE0005 -BE 1350 591 606 1

DRE0006 -BE 1350 577 592 1

DRE0014 -Marion 8050 571 586 1

DRE0027 -Marion 8050 596 611 1

DRE0029 -Marion 8050 605 621 1

Result 3,537 3,628 6

The Equipment is a Characteristic, with a hierarchy which is a dynamic list.

The CKF has the After Aggregation set in the Properties of the Calculated Key Figure.

In the Counter Column, i have set in properties to Counting All Values for the result.

Now the issue is this, if i create another Column with a formula in it.

CKF / Counter, i would like the following calculation to take place:

3,628 / 6

However what happens, is the formula for the Result seems to be 3,628 / 1

which is as if the "Counting All Values" does not take effect.

I will hopefully try a few setting changes, but thank you all for your replies so far.

Simon

[Former Member]

Attached is the communication from SAP whom have assisted in resolving this particular part of the overall issue. I hope it is useful to the community

15.10.2007 - 10:29:27 CET SAP Reply

Dear customer,

Thanks for your message.

I logoned on your OBA system and checked this query.

You set property "Calculate result as" to "Counting all values" for

key figure "counter", so that you got 6 in result row of "counter".

So I think this "Counting all values" setting is working.

It seems that you want to get the average value by 3,628 / 6 for the

formula that you described, I would rather suggest you to choose

"Calculate result as" to "Average of all values" for this formula.

Then you can get the desirable result. Or the result row will add

all single values together by default.

Hope above information helps you on this issue.

Best Regards,

Patricia Yang

Support Consultant - Netweaver Business Intelligence

SAP China

16.10.2007 - 06:49:24 CET SAP Reply

Dear customer,

Thanks for your update.

As for the 3 decimal place issue, it is described in attached note

959467. Would you please have a look at it? (especially point 3 and 4)

As of BW 3.5 SP18(yours is SP19), the behavior is changed.

And you will have at least 3 decimal places in your situation.

Best Regards,

Patricia Yang

Support Consultant - Netweaver Business Intelligence

SAP China

Info to SAP

16.10.2007 - 12:28:46 CET BW Basis Support Info for SAP

Hi Patricia,

Thank you for your reply,

is my understanding correct, that SP20 or the implementation of the

correction notes in OSS Note 995517 will remove the decimal places in

the Results rows of the query i have developed which has set "average

of all values" on the calculated key figure column.

I have also raised the following OSS Message, to which i mistakenly

left at Medium Priority but which should be set to High.

Can you please pick up the following message for your continued

diagnosis:

Suppress Hierarchy nodes for the display hierarchy ( 0000866034/2007 )

Thank you.

Simon Haddad

16.10.2007 - 07:46:40 CET SAP Reply

Dear customer,

For the situation 4 of note 959467, we have note 995517 which can

reverse this behavior back as desribed in note 959467 itself.

However for situation 3, we cannot change this behavior at this moment.

I am sorry that I didn't see your latter inquirement about the

hiding hierarchy row issue before I replied to you last time.

By your description, I am not sure why it doesn't work as you expect,

however since we should only have one issue in one message as stated

in note 50048, could you please create another message about this?

Thanks for your cooperation.

Best Regards,

Patricia Yang

Support Consultant - Netweaver Business Intelligence

SAP China

[Former Member]

Hi,

The result equals the total result if there is only one characteristic in the drilldown.

If there are several characteristics in the drilldown, there are different results that are combined to form an overall result.

Query result is the result of the key figure that results from the aggregation of all characteristics for the query.

so now unless we know what are the characteristics on which you are drilling down and what business logic u wanty we cannot choose from among the formulas :

SUMCT - Result

SUMGT - Overall result

SUMRT -Query Result

You could try all the three and check

Regards,

Aparna

[former_member188325]

Hi,y dont you provide an example with numeric values?