Technical data

Using Built-in Functio ns in Analytic Models Chapter 11
To calculate group averages of all membe rs that meet a condition, use an IF function as the expression, with
#N/A as the third argument. For example, to calculate average officer salaries by department, you could use
IF(IS_OFFICER, EMPLOYEE_SALARY, #N/A) instead of EMPLOYEE_SALARY in the formula above.
GROUPBY
Syntax
GROUPBY(Association)
Description
Use the GROUPB
Y function in a condition to g roup detail members by summary members (for example,
employees by
department). The argument must be an association data cube; otherwise, the function returns an
error.
Example
Suppose that an analytic model contains an association data cube called DEPARTMENTS, which associates
each employee with a particular department. The following formula for the EMPLOYEES_IN_DEPT cube
uses DCOUNT and GROUPBY to calculate the number of employees in each department:
DCOUNT(EMPLOYEES, GROUPBY(DEPARTMENTS))
The following formula for the AVG_ SALARY_BY_DEPT data cube uses DAVG and GROUPBY to calculate
the average salary for each department :
DAVG(EMPLOYEES, EMPLOYEE_SALARY, GROUPBY(DEPARTMENTS))
You can combine the GROUPBY function with other conditions. For example, the following formula for
the OFFICER_SALARIES_BY_DEPT cube uses the DSUM function to calculate the total officer salaries
in each department. By combining IS_OFFICER with the GROUPBY function, the formula ensures that
only officers are included in the sum:
DSUM(EMPLOYEES, EMPLOYEE_SALARY, GROUPBY(DEPARTMENTS) .AND. IS_OFFICER)
Note that DSUM(EMPLOYEES, EMPLOYEE_SALARY, GROUPBY(DEPARTMENTS)) is equivalent to
GROUPSUM(EMPLOYEES, EMPLOYEE_SALARY, DEPARTMENTS). Using DSUM with GROUPBY is
more flexible, because you can include other conditions, as shown in the formula above. On the other hand, the
GROUPSUM function calculates significantly faster. For this reason, if you want to sum by group and you do
not need to include other conditions, use the GROUPSUM function.
See Also
Chapter 11
, “Using Built-in F unctions in Analy t i c Models,” DCOUNT, page 150,Chapter 11, “Using Built-in
Functions
in Analytic Models,” DAV G, page 148,Chapter 11, “Usin g Built-in Functions in Analytic Models,”
DSUM, page
154 and Chapter 11, “Using Built-in Functions in Analytic Models,” GROUPSUM, page 1 62.
GROUPMAX
Syntax
GROUPMAX(
DimensiontoGroup, Expression, Association 1, {Association 2 ...} )
Descripti
on
Use the GROUPMAX function to maximize information by group. Expression contains the data to maximize.
The Association(s) indicate for which group(s) to maximize.
160 Copyright © 1988-2007, Oracl e. All rights reserved.