Hello all,
We have the following scenario in which the BEx Query designer behaves in an unexpected way. There is an infocube which contains the employee id, the calendar month and the payroll amount for this combination. There is also the cost center of the employee which will be drilldown characteristic in rows of the following query.
In the query we want to calculate the average payroll amount for a user-given calendar month interval drilled down in rows with the hierarchy of cost center -as already mentioned-, and as a second column the headcount of the employees whom the payroll amount is above average. For example, we have 3 employees in the Cost center A with the following salaries;
Employee A: 1000 euros
Employee B: 1500 euros
Employee C: 2500 euros
The average calculation is 1666,67 euros and consequently the correct headcount for cost center A is 2. The average payroll amount calcucation is implemented correctly with an exception aggregation of payroll amount to Employee characteristic with average of all non-zeros values.
For the headcount of the employees above average payroll we tried to implement a formula, let's call it "formula A", like the following;
payroll amount >= SUMRT, SUMCT, SUMGT (Average Payroll Amount) (all three options returned the same result).
The result for the cost center hierarchy display was a boolean "1" in every node as certainly there were employees with salary over the average of every node (see attached and compare with the result if we use employee in the drilldown).
If we used exception aggregation for the headcount formula A mentioned above, there was a warning that that operators like SUMCT etc. cannot be used with exception aggregation.
If anyone has any idea about this calculation it would be very helpful.
Thank you in advance,
Dimitris Kakogiannis
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.