Skip to Content
avatar image
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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

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

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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    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

    Add comment
    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,

      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