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.
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:
|Result||-113||0||7.3||This 7.3 is coming because 113/1551 *100 but it should be 111/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%|
|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|
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:
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.
Another reference link for the similar topic is:
I saw that you raised this question on forum. Finally you have shared the steps you followed to achieve that. This is really nice. 🙂
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.
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..
Yes, it is taking the overall result values to calculate that sub total % column value.
It is updated. Thanks for pointing it out.
Nice and Good article. If you add some pics form Bex designer and results, Your doc will be more effective. Thanks for sharing.
I will revise with some more screenshot from BeX as requested/suggested by you during weekend.
Thanks for your encouragement.
Thanks for considering. Keep sharing useful scenarios.
Nice Document alok... 🙂 you faced this problem and then shared your experience will all...
🙂 its really very nice.
Yes, thanks for your good words. 🙂
Informative piece of doc.Thanks for sharing and looking forward next doc.
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.
Kindly explain that as it is important for somebody who does not know the functionality of constant selection
Thanks for your feedback.
I will take into consideration of all the points brought up by you.
Thanks and Regards,
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.
Thanks for the encouragement!
Nice to remain updated with a tactic.
Its very good case study. Its useful while working with query 🙂
nice work.. if oyu put the tables in an actual table it would be more readable..
will try to incorporate your suggestion!
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 🙂
Hi Alok, Good that you have inked this out. Excellent solution indeed.
Keep it up.
Thanks a lot Laxmi for the words of encouragement.
Good solution Alok...Good work..:)
Thanks for sharing