5.4.2  Creating a Calculated Field from Aggregate Data

To create a new calculated field from aggregate data, follow the steps below:

1. In the Viewsheet Component panel, right-click on the Data Block, data model, or query to which you want to add a calculated field. Select 'New Calculated Field' from the context menu.

 

This opens the 'Calculated Field' dialog box.

Note: The selection of 'Detail' or 'Aggregate' is a one-time choice. You cannot convert between these two types at a later time.

2. In the 'Name' field, enter a name for the new calculation field.

 

3. Select the 'Aggregate' option and click 'OK'. This opens the Formula Editor.

The 'SQL' option is disabled because aggregate-based cal­culations require Java­Script syntax.

4. In the 'Return Data Type' menu, select the data type that the calculated field will return.

5. In the top-left panel, click the 'New Aggregate' button.

 

This opens the 'Aggregate' dialog box.

6. In the 'Field' menu of the 'Aggregate' dialog box, select the field that you wish to aggregate for purposes of the calculation.

 

7. In the 'Aggregate' menu, select the desired aggregation method. (See Aggregation Options for information about the available methods.)

8. For a bivariate aggregation method, select the second field from the 'With' menu.

9. Click 'OK' to close the 'Aggregate' dialog box. The Formula Editor now displays the newly defined aggregate quantity in the top-left panel.

 

10. (Optional) Click the 'New Aggregate' button again, and repeat the above steps to add as many additional aggregates as desired.

Click an aggregate field in the top-left panel to add it to the expression.

11. Once you have defined the required aggregates in the top-left panel, enter the desired calculation (using the aggregate fields) in the formula field at the bottom of the dialog box.

 

12. Click 'OK' to close the Formula Editor. This adds the new calculated field to the data source tree in the Component panel.

 

You cannot bind an aggregate-based calculated field to Selection Components.

You can bind this calculated field to Output Components or Data View Components in the same way that would bind any other data field.

Example: Aggregate-Level Calculation  >>

Consider a Viewsheet that is based on the sample 'Sales Explore' Worksheet. In this example, you will analyze how the percentage of paid orders varies according to different dimensions (company, state, date, etc.).

Observe that “percentage of paid orders” for a given group is an aggregate quantity. It represents the ratio of paid orders to total orders for the group, and therefore requires a count (aggregate) of both the paid orders and total orders for every individual group. These counts are not static, but will vary dynamically based on the requested dimension (state, company, date, etc.).

In many cases it is difficult to configure an underlying Worksheet to produce these aggregate results in a manner conducive to dynamic Viewsheet analysis. Though a Worksheet provides all of the required aggregation features, multi-dimensional aggregations within a Worksheet often require the creation of multiple Data Blocks, and this ultimately limits the interactivity of the Viewsheet. Therefore, it is best to implement aggregate-based calculations at the Viewsheet level.

Follow the steps below to create a Viewsheet that implements aggregate calculations to express the percentage of paid orders for various groupings.

1. Create a new Viewsheet based on the 'Sales' > 'Sales Explore' Data Worksheet. (See Quick Start: Creating a Viewsheet for information.)

2. In the Viewsheet Component panel, right-click the 'Sales' data block and select 'New Calculated Field' from the context menu.

 

This opens the 'Calculated Field' dialog box.

3. In the 'Name' field of the 'Calculated Field' dialog box, enter the text “Percent Paid”.

4. Select the 'Aggregate' option and click 'OK'. This opens the Formula Editor.

 

5. In the Formula Editor, follow the steps below:

a. In the top-left panel, expand the 'Fields' node, and click 'New Aggregate'.

 

This opens the 'Aggregate' dialog box.

The 'Paid' field con­tains a “1” (order paid) or a “0” (order not paid). This sum therefore provides the absolute number of paid orders.

b. In the 'Field' menu of the 'Aggregate' dialog box, select the 'Paid' field. This is the field whose values you will sum to obtain the number of paid orders.

 

c. In the 'Aggregate' menu, select 'Sum'.

d. Click 'OK' to close the 'Aggregate' dialog box. The Formula Editor displays the newly defined “Sum(Paid)” aggregate field under the 'Fields' node in the top-left panel.

 

e. Click 'New Aggregate' again.

f. In the 'Field' menu of the 'Aggregate' dialog box, again select the 'Paid' field.

The 'Count' provides the total number of paid and unpaid orders.

g. In the 'Aggregate' menu, select 'Count' and click 'OK'.

 

The Formula Editor displays the newly defined 'Count(Paid)' aggregate field in the top-left panel together with the existing 'Sum(Paid)' field.

h. In the top-left panel, click on the 'Sum(Paid)' field to enter the field into the expression.

i. Type a division (“/”) symbol, or select this symbol from the top-right 'Operators' panel (under the 'Arithmetic' group).

j. In the top-left panel, click on the 'Count(Paid)' field to enter the field into the expression.

k. In the 'Return Data Type' menu, select 'Float'.

 

l. Click 'OK' to close the Editor. This adds the new 'Percent Paid' calculated field to the 'Sales' data block.

 

6. From the Component panel, drag a Chart component into the Viewsheet.

7. Click the 'Edit' button on the new Chart to open the Chart Editor.

8. In the Data Source panel, expand the 'Dimensions' folder under the 'Sales' data block.

9. Drag the 'Category' field from the Data Source panel to the 'X' region in the Data panel.

Note: An aggregate-based calculated field is always considered a measure.

10. In the Data Source panel, expand the 'Measures' folder.

11. Drag the 'Percent Paid' field from the Data Source panel to the 'Y' region in the Data panel. The Chart Y-axis now displays the fraction of orders paid on a category-by-category basis.

12. To change the Y-axis labels to a “percent” representation, right-click the axis labels, and select 'Format' from the context menu. This opens the 'Format' dialog box.

 

13. In the 'Format' dialog box, click the Format tab, and select the 'Percent' option. Click 'OK' to close the dialog box. This displays the labels in percent format.

 

You can use the Chart Editor to bind other dimensions to the X-axis. For example, drag 'Name' from the Data Source panel to the 'X' region of the Data panel to analyze the percent paid according to product name. Drag both the 'State' and 'City' fields to the 'X' region to analyze percent paid on a geographical basis.

If you provide the end user with access to the Chart Editor (see Enabling End-User Chart and Crosstab Editing), they can use exactly the same method to analyze the data. However to make the analysis even easier for the user, you can add a menu that allows the user to directly select the desired dimension for the X-axis. To do this, continue with the steps below:

14. Close the Chart Editor. (Click the red 'X' in the top-right corner.)

15. From the Viewsheet Component panel, drag a ComboBox component into the Viewsheet.

16. Right-click on the new ComboBox, and select 'Properties' from the context menu. This opens the 'ComboBox Properties' dialog box.

17. In the 'Name' field, enter 'Dimension'.

 

18. In the 'List Values' region, select the 'Embedded' option, and click the 'Edit' button. This opens the 'Embedded List Values' dialog box.

19. Enter the following labels/values: 'Category', 'Date', 'Company', 'Name', 'City', and 'State'.

 

20. Click 'OK' to close the 'Embedded List Values' dialog box. Click 'OK' to close the 'ComboBox Properties' dialog box.

21. Click the 'Edit' button on the Chart to reopen the Chart Editor.

22. In the Data panel, click the arrow button next to the 'X' region, and select 'Variable' from the menu. This enables the variable menu in the 'X' region.

 

23. From the variable menu, select the '$(Dimension)' option. This will use the ComboBox to set the dimension displayed on the X-axis.

 

Explore the data by making different selections in the ComboBox. Investigate how percent paid varies according to dimensions such as state, city, category, date, etc. When you analyze the data along the 'Date' dimension, try drilling down to explore finer levels of resolution (e.g., month). See Drilling Down into a Chart for more details.

 

 

Example: Calculation with String Return Value  >>

You can create an aggregate calculation to return a string-valued measure. You can then bind this measure to a 'Visual' region (color, shape, size) to produce a categorical visual grouping.

Follow the steps below to create a Viewsheet that uses a string-valued measure to categorize groups as having a low average discount rate (less than 1.5%) or high discount rate (greater than 1.5%).

1. Create a new Viewsheet based on the 'Sales' > 'Sales Explore' Data Worksheet. (See Quick Start: Creating a Viewsheet for information.)

2. In the Viewsheet Component panel, right-click the 'Sales' data block and select 'New Calculated Field' from the context menu.

 

This opens the 'Calculated Field' dialog box.

3. In the 'Name' field of the 'Calculated Field' dialog box, enter the text “Discount Class”.

4. Select the 'Aggregate' option and click 'OK'. This opens the Formula Editor.

 

5. In the Formula Editor, follow the steps below:

a. In the top-left panel, expand the 'Fields' folder and click the 'New Aggregate' button.

 

This opens the 'Aggregate' dialog box.

The 'Paid' field con­tains a “1” (order paid) or a “0” (order not paid). This sum therefore provides the absolute number of paid orders.

b. In the 'Field' menu of the 'Aggregate' dialog box, select the 'Discount' field. This is the field whose values you will average to obtain the average discount.

 

c. In the 'Aggregate' menu, select 'Average'.

d. Click 'OK' to close the 'Aggregate' dialog box. The Formula Editor displays the newly defined “Average(Discount)” aggregate field in the top-left panel.

 

Hint: Click the 'Aver­age(Discount)' field in the top-left panel to enter the field into the expression.

e. Enter the following script in the Script Editor:

if (field['Average([Discount])']<.015) {

  'Low';

}

else {

  'High';

}

f. In the 'Return Data Type' menu, select 'String'.

 

g. Click 'OK' to close the Editor. This adds the new 'Discount Class' calculated field to the 'Sales' data block.

 

6. From the Component panel, drag a Chart component into the Viewsheet.

7. Click the 'Edit' button on the new Chart to open the Chart Editor.

8. In the Data Source panel, expand the 'Dimensions' folder under the 'Sales' data block.

9. Drag the 'Category' field from the Data Source panel to the 'X' region in the Data panel.

Note: An aggregate-based calculated field is always considered a measure.

10. In the Data Source panel, expand the 'Measures' folder.

11. Drag the 'Quantity Purchased' field from the Data Source panel to the 'Y' region in the Data panel.

12. Drag the 'Discount Class' field from the Data Source panel to the 'Color' region in the Visual panel. This discriminates groups based on the string values returned by the 'Discount Class' calculated field.

 

 

<< 5.4.1 Creating a Calculated Field from Detail Data © 1996-2013 InetSoft Technology Corporation (v11.5) 5.4.3 Editing a Calculated Field >>