GROUPSUM

Syntax

GROUPSUM (Dimension to Group, Expression, Association 1, {Association 2 ...})

Description

Use the GROUPSUM function to sum information by group. Expression contains the data to sum. The Association(s) indicate what group(s) to sum by.

Example

The following examples provide uses of the GROUPSUM function.

Example 1

For example, suppose you want to sum employee salaries by department. Create an analytic model definition that contains the following data cubes:

  1. EMPLOYEE_SALARY, which uses a dimension called EMPLOYEES.

    This data cube contains the salary for each employee.

  2. DEPARTMENT_SALARY, which uses a dimension called DEPARTMENTS.

    This data cube contains the total salaries for each department.

  3. An association data cube called EMPLOYEE_DEPT, which associates each employee with a particular department.

Calculate DEPARTMENT_SALARY with the following formula:

GROUPSUM(EMPLOYEES, EMPLOYEE_SALARY, EMPLOYEE_DEPT)

You can read this formula as follows: Sum the employees' salaries by department.

Example 2

The GROUPSUM function can also perform more complex groupings. For example, suppose you want to sum employee salaries by branch and department. To do this, perform the following additional steps:

  1. Create a dimension called BRANCHES, which contains a dimension of the branches.

  2. Create an association data cube called EMPLOYEE_BRANCH, which associates the EMPLOYEES dimension with the BRANCHES dimension.

  3. Define a data cube called SALARY_BY_BRANCH_AND_DEPT, which uses both the BRANCHES and DEPARTMENTS dimensions. Calculate this data cube with the following formula: 

    GROUPSUM(EMPLOYEES, EMPLOYEE_SALARY, EMPLOYEE_BRANCH, EMPLOYEE_DEPT)

You can read this formula as follows: Sum the employees' salaries by branch and department.

As this example demonstrates, you can summarize detail information for a combination of dimensions by using an association for each dimension.

Example 3

In many cases, it is useful to summarize information by date. In these cases, use a data cube with a Date format instead of an association.

For example, suppose you want to summarize sales information by product and month. Create an analytic model definition that contains the following dimensions:

  1. TRANSACTIONS, which contains a series of sales transactions.

  2. PRODUCTS, which contains a dimension of products.

  3. MONTHS, which contains a series of months.

Define the following data cubes:

  1. SALE_AMOUNT, which uses the TRANSACTIONS dimension.

    This data cube contains the amount of each sale.

  2. An association data cube called PRODUCT_SOLD, which associates TRANSACTIONS with PRODUCTS.

  3. SALE_DATE, which uses the TRANSACTIONS dimension and the YYYY/MM/DD format. This data cube contains the date for each transaction.

  4. MONTHLY_SALES, which uses the PRODUCTS and MONTHS dimension. Calculate this data cube with the following formula:

    GROUPSUM(TRANSACTIONS, SALE_AMOUNT, PRODUCT_SOLD, SALE_DATE)

You can read this formula as follows: Sum the transactions' sale amounts by product and sale date. Because SALE_DATE is Date formatted (YYYY/MM/DD), the GROUPSUM function knows to sum by date.

To calculate group sums of all members which meet a condition, use an IF function as the expression, with #N/A as the third argument. For example, use IF(Valid, SALE_AMOUNT, #N/A) instead of SALE_AMOUNT in the MONTHLY_SALES formula above.