Skip to Content
Author's profile photo Former Member

Case Study on proper usage of SUMCT & Exception aggregation rules in calculating % CKF

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.

Assigned tags

      34 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member
      Blog Post Author
      Author's profile photo Suman Chakravarthy K
      Suman Chakravarthy K

      Hi Shyam,

      I saw that you raised this question on forum. Finally you have shared the steps you followed to achieve that. This is really nice. 🙂

      Regards,

      Suman

      Author's profile photo Former Member
      Former Member
      Blog 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

      Author's profile photo Suman Chakravarthy K
      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

      Author's profile photo Former Member
      Former Member
      Blog 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

      Author's profile photo CH Raman
      CH Raman

      Hi Shyam,

      Nice and Good article. If you add some pics form Bex designer and results, Your doc will be more effective. Thanks for sharing.

      Thanks

      Author's profile photo Former Member
      Former Member
      Blog 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

      Author's profile photo CH Raman
      CH Raman

      Thanks for considering. Keep sharing useful scenarios.

      Thanks

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Yes, sure.

      Author's profile photo Satendra Mishra
      Satendra Mishra

      Nice Document alok... 🙂 you faced this problem and then shared your experience will all...

      🙂 its really very nice.

      Regards,

      SM

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Satendra,

      Yes, thanks for your good words. 🙂

      Best Regards,

      Shyam Alok

      Author's profile photo Former Member
      Former Member

      Informative piece of doc.Thanks for sharing and looking forward next doc.

      Arshiyan

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Arshiyan!

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member
      Blog 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

      Author's profile photo Ashok Babu Kumili
      Ashok Babu Kumili

      Shyam Alok,

      Thank you so much for this blog. This is very cool. I specifically love that it is SOLUTIONS oriented.  This is so organised and well described.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Ashok,

      Thanks for the encouragement!

      Best Regards,

      Shyam Alok

      Author's profile photo Former Member
      Former Member

      Nice to remain updated with a tactic.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Ajax.

      Author's profile photo Ganesh Bothe
      Ganesh Bothe

      Hi shyam,

      Its very good case study. Its useful while working with query 🙂

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Ganesh.

      Author's profile photo Martin Grob
      Martin Grob

      nice work.. if oyu put the tables in an actual table it would be more readable..

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Martin!

      will try to incorporate your suggestion!

      Author's profile photo Former Member
      Former Member

      Nice Work.

      Regards,

      Sushant

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Sushant!

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Former Member
      Former Member

      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 🙂

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Neha!

      Author's profile photo Former Member
      Former Member

      Hi Alok, Good that you have inked this out. Excellent solution indeed.

      Keep it up.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks a lot Laxmi for the words of encouragement.

      Author's profile photo Former Member
      Former Member

      Good solution Alok...Good work..:)

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Naveen!

      Author's profile photo Sandeep a
      Sandeep a

      Thanks for sharing

      regards

      sandeep

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Sandeep!