# 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

You must be Logged on to comment or reply to a post.
Former Member
Blog Post Author

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

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

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

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

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

Former Member
Blog Post Author

Hi Raman,

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

Best Regards,
Shyam Alok

Thanks for considering. Keep sharing useful scenarios.

Thanks

Former Member
Blog Post Author

Yes, sure.

Nice Document alok... ðŸ™‚ you faced this problem and then shared your experience will all...

ðŸ™‚ its really very nice.

Regards,

SM

Former Member
Blog Post Author

Hi Satendra,

Yes, thanks for your good words. ðŸ™‚

Best Regards,

Shyam Alok

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

Arshiyan

Former Member
Blog Post Author

Thanks Arshiyan!

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

Former Member
Blog Post Author

Hi Ashutosh,

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

Thanks and Regards,

Shyam Alok

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.

Former Member
Blog Post Author

Hi Ashok,

Thanks for the encouragement!

Best Regards,

Shyam Alok

Nice to remain updated with a tactic.

Former Member
Blog Post Author

Thanks Ajax.

Hi shyam,

Its very good case study. Its useful while working with query ðŸ™‚

Former Member
Blog Post Author

Thanks Ganesh.

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

Former Member
Blog Post Author

Thanks Martin!

will try to incorporate your suggestion!

Nice Work.

Regards,

Sushant

Former Member
Blog Post Author

Thanks Sushant!

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---

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 ðŸ™‚

Former Member
Blog Post Author

Thanks Neha!

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

Keep it up.

Former Member
Blog Post Author

Thanks a lot Laxmi for the words of encouragement.

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

Former Member
Blog Post Author

Thanks Naveen!

Thanks for sharing

regards

sandeep

Former Member
Blog Post Author

Thanks Sandeep!