Query exception aggregation with SUMCT, SUMRT, SUMGT

Former Member — Mon, 20 Oct 2014 21:47:22 GMT

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

Re: Query exception aggregation with SUMCT, SUMRT, SUMGT

KodandaPani_KV — Tue, 21 Oct 2014 03:53:27 GMT

Hi,

Result

SUMCT <Operand>

Delivers the result of the operand in all rows or columns (see

Percentage Functions %CT).

Overall Result

SUMGT<Operand>

Delivers the overall result of the operand (see Percentage Functions

%GT).

Report Result

SUMRT<Operand>

Delivers the report result of the operand (to distinguish between the

overall and the report result, see also Percentage Functions %GT and

%RT)

please find the below link.

http://help.sap.com/saphelp_nw70/helpdata/EN/03/17f13a2f160f28e10000000a114084/content.htm

Problems with the SUMGT and SUMRT or %GT and %RT operators

Thanks,

Phani.

Re: Query exception aggregation with SUMCT, SUMRT, SUMGT

Former Member — Tue, 21 Oct 2014 07:20:39 GMT

Hello,

thank you for your theoritical feedback I am already aware of them and they don't solve my issue.

Dimitris

Question Re: Query exception aggregation with SUMCT, SUMRT, SUMGT in Technology Q&A

Query exception aggregation with SUMCT, SUMRT, SUMGT

Re: Query exception aggregation with SUMCT, SUMRT, SUMGT

Re: Query exception aggregation with SUMCT, SUMRT, SUMGT