Former Member

### Creating a Formula on an Exception aggregation Total Column

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

10|10000 characters needed characters exceeded

• Former Member
Oct 09, 2007 at 08:04 AM

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

10|10000 characters needed characters exceeded
• Former Member
Oct 09, 2007 at 08:25 AM

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

10|10000 characters needed characters exceeded
• Former Member
Oct 09, 2007 at 01:08 PM

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

10|10000 characters needed characters exceeded
• Former Member 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,

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,

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,

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.

diagnosis:

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

Thank you.

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