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

[Former Member]

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.