Exception aggregation and Result Lookup formula in SAP Analytics on Cloud
Calculation of Salary & Wages for an employee based on hours and pay rates while employees are grouped based on department hierarchy they belong to report correct numbers.
When we create formula for an account in SAP Business Objects for Cloud it is applied at all levels of hierarchy for other dimensions. So we have to be careful while using those calculation in stories since the combination of hierarchy of a dimension and formula in account dimension can work together properly. So we will see a common business scenario to check the behavior of exception aggregation. Its behavior is similar to exception aggregation functionality in BEX queries (For people from BW background).
Salary & Wages = Pay Rates * Hours
Exception Aggregation created in account dimension of the model. We get a variety of options to choose like SUM, COUNT, MIN, MAX, AVG etc. in Exception aggregation column and need to choose which set of dimension we want summation to happen.
Following scenario shows that without exception aggregation instead of summing up the salary for Ramanathan, Tom, Alex for Management group, it picks up the value at the parent level and calculates amount.
Management Salary (8236800) is calculated from Pay Rates (1320) * Monthly Hours (6240) on aggregated value while the correct for Management Salary (228800) is to sum up directly (1248000+998400+499200) as per hierarchy. So we use exception aggregation to arrive at correct result.
Without Exception aggregation in Employee and Time dimension:
Result Lookup formula that could use in formula section of account dimension. There was some requirement where we want to do a look up of a set of data and populate it into another account in the same model.
Syntax: ResultLookup (<Account member>, <d/Dimension>=”Dimension Member”)
Example: ResultLookup ([V0013], [d/Employee]=”Tom”)