Skip to Content

Problem Statement & Scenario:

In a report, there is a requirement to show the % of values as per following logic:

1st KPI gives Value 1, 2nd KPI gives Value 2 and 3rd should be a % value.

Now the logic for % = Value 1 (individual row value) / subtotal of Value 2 in one period * 100

and when it come to result or subtotal rows, it should give in the % column: subtotal of value 1 / subtotal of value 2 * 100.

For overall result; % = Overall result of value 1 / overall result of value 2 * 100.

Also it should take into consideration of the absolute values in the % with no -/+ sign.

Proposed Solution:

At first sight, generally, developer would use the following formula; i.e.

% = Value 1 / SUMCT(Value2) * 100.

But it will give correct value for each row values except for result/overall result rows.

In the result rows, instead of taking the subtotal of values 2 in the calculation, it rather takes overall result of values 2 and hence the value goes wrong.

Below is the example:

Year Month Mat Value1 Value2 %
2013 1 ABC -2 0.1
XYZ -50 3.2
MBD -2 0.1
GFH -4 0.3
TYU -30 1.9
OPR -5 0.3
lOP -20 1.3
Result -113 0 7.3 This 7.3 is coming because 113/1551 *100 but it should be 111/0 = 0
2 ABC 0 0
XYZ -1 0.1
MBD -19 1.2
GFH -5 0.3
TYU -1 0.1
OPR -130 8.4
lOP -162 10.4
WER 0 0
Result -318 386 20.5 Similarly, 20.5 is coming because 318/1551*100 but it should be coming as 318/386*100 = 82.4%
3 ABC -1 0.1
XYZ -9 0.6
MBD -13 0.8
GFH -1 0.1
TYU -383 24.7
OPR -50 3.2
lOP -1 0.1
WER -10 0.6
MNO 0 0
Result -468 1,165.00 30.2 Similarly, 30.2 is coming because 468/1551*100; but it should be coming as 468/1165*100 = 40.2
Overall Result -899 1,551.00 58

So to achieve the right result:

Following steps would be required:

Step 1: create a RKF or local selection (hidden) to be able to populate sub total values of values 2 against each values of values 1 in the rows. This is only for calculation purpose.

It can be achieved by making MAT as a constant selection in this RKF or local selection.

Step 2: Create a formula (F1) to include nodim(above RKF value or Local Selection Value) with exception aggregation on the ‘MAT’. (Hide this)

Step 3: Create another formula (F2) then to do ABS(F1) with exception aggregation on Period. (hide this)

Step 4: Create another Formula (Final % Value) as per following formula:

               NDIV0((ABS(Value 1) / ABS(F2)*100))+0 and with no exception aggregation on it.

After doing this we will get the correct result values in % column as desired.

That would be:

Year Month Mat Value1 Value2 %
2013 1 ABC -2 0.1
XYZ -50 3.2
MBD -2 0.1
GFH -4 0.3
TYU -30 1.9
OPR -5 0.3
l -20 1.3
Result -113 0 0
2 ABC 0 0
XYZ -1 0.1
MBD -19 1.2
GFH -5 0.3
TYU -1 0.1
OPR -130 8.4
l -162 10.4
WER 0 0
Result -318 386 82.4
3 ABC -1 0.1
XYZ -9 0.6
MBD -13 0.8
GFH -1 0.1
TYU -383 24.7
OPR -50 3.2
l -1 0.1
WER -10 0.6
MNO 0 0
Result -468 1,165.00 40.2
Overall Result -899 1,551.00 58

This way we can achieve this kind of unique requirement of calculations.

Please note that, key point is the usage of expectation aggregation here based on the sub total requirement and various steps to achieve the final % values and it is not a straight one step solution.

To report this post you need to login first.

34 Comments

You must be Logged on to comment or reply to a post.

    1. Shyam Alok Post author

      Hi Suman,

      Yes, This was one of the good learning resolving this issue and I documented it be available with our SCN community users/friends for future reference.

      Thanks for encouragement and mutual support.

      Best Regards,

      Shyam Alok

      (0) 
      1. Suman Chakravarthy K

        Hi Shyam,

        In Month3 explanation, you have mentioned “Similarly, 30.2 is coming because 468/1165*100; but it should be coming as 468/1165*100 = 40.2 “.

        Actually 30.2 is coming because 468/1551*100, right? Please check..

        Regards,

        Suman

        (0) 
        1. Shyam Alok Post author

          Hi Suman,

          Yes, it is taking the overall result values to calculate that sub total % column value.

          It is updated. Thanks for pointing it out.

          Best Regards,

          Shyam Alok

          (0) 
    1. Shyam Alok Post author

      Hi Raman,

      I will revise with some more screenshot from BeX as requested/suggested by you during weekend.

      Thanks for your encouragement.

      Best Regards,
      Shyam Alok

      (0) 
  1. ASHUTOSH SINGH

    Hi Shyam,

    A good effort i must say .

    But it needs a lot of restructuring.

    Anyways coming to the main point . You have not explained what is the use of constant selection in your document . What is happening by using constant selection functionality.

    Following steps would be required:

    Step 1: create a RKF or local selection (hidden) to be able to populate sub total values of values 2 against each values of values 1 in the rows. This is only for calculation purpose.

    It can be achieved by making MAT as a constant selection in this RKF or local selection.

    Kindly explain that as it is important for somebody who does not know the functionality of constant selection

    Regards,

    Ashutosh

    (0) 
    1. Shyam Alok Post author

      Hi Ashutosh,

      Thanks for your feedback.

      I will take into consideration of all the points brought up by you.

      Thanks and Regards,

      Shyam Alok

      (0) 
    1. Pallavi Pande

      how are you?

      I need some help from you-

      Saw couple of posts you posted on SDN and hence writing you this queries

      I am about to migrate our system from 3.5 to 7.3

      Upgrade is already completed. NOw i want to migrate the flow using the Migration tool from 3.x to 7.3

      My doubt is: when this moved to Production, how do i take care of the GO lIVE

      HOw about the delta for logistics extractor? Do we need to stop the posting from ECC? setup ,re-initiaization etc..?

      Do i need to bother about the delta datasources or its taken care by itself?

      Also if you have done this task earlier please let me know the impact analysis i should do for this—

      Please guide

      (0) 
  2. Neha Kumari

    Hi Alok , IT is a very nice document to understand how the aggregation works in different situations.

    And the standard function provided could give you weired output some time 🙂

    (0) 

Leave a Reply