Understanding Exception Aggregation via Examples

[himanshu_sarawagi2]

Hello Everyone, i feel one of the most Complex/Confusing topic in BEx (SAP BI/BW) is Exception Aggregations. Lets try to understand various Exception Aggregation available in BEx Designer via Examples. (This blog does not cover Hierarchy related Exception Aggregation)

 

Scenario : Want to know the qty at Company Code level and Company Code + Material Group Level.

 

First we will try to understand (With Example) what is the basic difference between standard aggregation (Summation) and exception aggregation (Summation).



 

Output for above KF's :



 

As we can see there is difference in the output however the definition of both the KF are same. Only difference is one is enabled with exception aggregation on Material Group.

KF with Standard Aggregation works on individual row line. (Denoted with Orange lines)
For example : AR company code satisfies the 500K expression Hence the output 1.

Exception Aggregation works with a Ref CHARACTERISTIC (Material Group) on which the calculation will be done.
KF with Exception Aggregation show the value 7 (Green Boxed Value) i.e 7 Material groups in company code AR satisfies 500K expression.Basically, the calculation is done by keeping the Ref CHARACTERISTIC in Drill down but the output is shown without the ref characteristic in the layout.

Lets check the below screenshot for the same.



Once you drill down on the Material Group, we can observe out of 9 Material Groups 7 satisfies the 500K Expression.

Now just keep only Material Group in the drill down to get the Total value at overall Company Code level (Blue Boxed Value).



Note : the value for Material Group is changed as value from all the company code are summed up.

The behavior is similar for below mentioned Exception Aggregation (Kindly follow above understanding for below screenshots in sequential manner)

1. Max (Exception Aggregation "Maximum" With Ref Char on Material Group)


2. Min (Exception Aggregation "Minimum" With Ref Char on Material Group)


3. >1 R (Exception Aggregation "Exception If more than One Record Occurs" With Ref Char on Material Group)


4. >1 V (Exception Aggregation "Exception If more than One Value Occurs" With Ref Char on Material Group)


5. >1 V <>0 (Exception Aggregation "Exception If more than One Value <>0 Occurs" With Ref Char on Material Group)


6. Avg (Exception Aggregation "Average" With Ref Char on Material Group)


7. Avg <>0 (Exception Aggregation "Average of Detailed Values That are Not Zero, Null or Error " With Ref Char on Material Group)


8. Counter (Exception Aggregation "Counter for All Detailed Values" With Ref Char on Material Group)


9. Counter <>0 (Exception Aggregation "Counter for All Detailed Values That are Not Zero, Null or Error " With Ref Char on Material Group)


10. 1st Value (Exception Aggregation "First Value" With Ref Char on Material Group)


11. Last Value (Exception Aggregation "Last Value" With Ref Char on Material Group)


12. Std Dev (Exception Aggregation "Standard Deviation" With Ref Char on Material Group)


13. Variance (Exception Aggregation "Variance" With Ref Char on Material Group)

Output at Company Code :



Output at Company Code and Material Group :



Output at Material Group :



 

The Above behavior is not followed by "Average Weighted With Calendar Days" and "Average Weighted With Working Days" Exception Aggregation.

For Exception Aggregation to work on "Average Weighted With Calendar Days" and "Average Weighted With Working Days" requires a Non-Cumulative KF with reference char as 0CALDAY.

Below is the example for "Average Weighted With Calendar Days" and "Average Weighted With Working Days" :



Drilling Down on Calday will explain how the output is calculated :

 



 

Calculation Logic : The Amount or Quantity kept each day divided by no of days.

For Average Weighted With Calendar Days : 475000/31 = 15323.

For Average Weighted With Working Day : 350000/22 = 15909.

Note : For Working Day, consider the Amount/Quantity only for working day (Weekdays).

 

Thank You.