Before You Begin

This tutorial shows you how to create group calculations for attributes in your workbook.

Background

A group calculation contains members of an attribute such as City divided into smaller named groups. This tutorial shows you how to use group calculations in a workbook.

You can use group calculation to build groups without writing long expressions to create the groups. Oracle Analytics writes the expression used to create the group calculation in the background. If you have advanced expression writing skills, you can copy the expression, edit it, and create additional group calculations.

This tutorial shows you the steps to create groups in your workbook but doesn't select all of the available values for the demonstration. If you don't assign all of the values to a group, you must select Include Others in the Group Calculation dialog. Oracle Analytics shows a histogram of the values assigned to a group calculation and the unassigned values.

What Do You Need?

Create a Dataset

In this section, you create a dataset to use in the workbook.

  1. Sign in to Oracle Analytics.
  2. On the Home page, click Create and click Dataset.
  3. In Create Dataset, click Drop data file here or click to browse. In File Upload, select sample_order_lines2023.xlsx and click Open.
  4. In Create Dataset Table from sample_order_lines2023.xlsx, click OK.
  5. In New Dataset, click Save Save icon. In Save Dataset As, enter sample_order_lines2023 and click OK.


    Description of sol2023_join_diagram.png follows
    Description of the illustration sol2023_join_diagram.png
  6. Click Create Workbook. Close the Insights panel.

Define Group Calculation

In this section, you create a group calculation.

  1. In the Data Data pane icon pane, right-click City and select Create Group Calculation. In Group Calculation, enter Cities by Regions as the group name.
  2. Enter North America to replace Group 1. In the City list of values, click the following:
    • Albany
    • Augusta
    • Austin
    • Baltimore
    • Birmingham
  3. Click Group Add group icon. Enter Asia in the empty group field. In the City list of values, click the following:
    • Ahmedabad
    • Ahmednagar
    • Bangalore
    • Bangkok
    • Beijing
  4. Click Include Others.


    Description of calc_groups.png follows
    Description of the illustration calc_groups.png
  5. Click Save.

    6. Oracle Analytics adds the Cities by Regions group calculation to the My Calculations folder.

    Description of cities_by_region_calc.png follows
    Description of the illustration cities_by_region_calc.png
  6. In the Data Data pane icon pane, hold down the Ctrl key, select Sales and Cities by Region, and then drag them to the canvas.


    Description of sales_by_city_regions.png follows
    Description of the illustration sales_by_city_regions.png
  7. Click Save Save icon. In Save Workbook, enter a Name and click Save.

Create Another Group Calculation

In this section, you create a group calculation from the My Calculations folder.

  1. In the Data Data pane icon pane, right-click My Calculations and select Create Group Calculation.
  2. In Group Calculation, select City from sample_order_lines2023. In Name, enter City by US Region.
  3. Enter Northeast to replace Group 1. In the City list of values, click the following:
    • New York
    • New York City
    • Albany
  4. Click Group Add group icon. Enter Midwest in the empty group field. In the City list of values, click the following:
    • Chicago
    • Columbus
    • Des Moines
  5. Click Group Add group icon. Enter Southwest in the empty group field. In the City list of values, click the following:
    • Los Angeles
    • Austin
    • Dallas
  6. Click Include Others and click Save.
  7. In the Data Data pane icon pane, hold down the Ctrl key, select Profit and City by US Region, and then drag them to the canvas.


    Description of profit_us_regions.png follows
    Description of the illustration profit_us_regions.png
  8. Click Save Save icon.

Remove Others from the Visualizations

In this section, you filter out the data values that weren't included in the defined groups. If all of the attribute's values are assigned to a group, you won't have a large Others group.

  1. Select the Sales by Cities by Regions visualization. In the Data Data pane icon pane, drag Cities by Regions to Filters in the Grammar pane.
  2. In Cities by Regions filter dialog, click Others and click Exclude (x).
  3. Select the Profit by City by US Region visualization. In the Data Data pane icon pane, drag City by US Region to Filters in the Grammar pane.
  4. In City by US Region filter dialog, click Others and click Exclude (x).


    Description of groups_wo_others.png follows
    Description of the illustration groups_wo_others.png
  5. Click Save Save icon.

Learn More