Calculating a weighted average

The Weighted Average action allows you to group values from multiple rows to calculate an average value of a numeric field weighted by a defined factor, creating a new output record set that can be written to a new or existing table. You can:

• Group by one or more fields
• Apply a filter to the input record set or a post weighted average filter on the output record set
• When using an existing table as the output, use field mapping to map output fields to the existing table structure.

On the Configuration tab:

1. Drag Table Fields to define filters, field grouping, fields being averaged, and weight factor.

2. Define a filter to limited the averaged records.

3. Define the fields by which data is being grouped.

4. Define the fields to be averaged.

5. Defined the field used for the weight factor.

6. Define a filter to limit the weighted average results.

7. Preview the data or SQL statement.

Possible applications include:

• Calculate the average sales price for a product for sales of different quantities at different prices
• Calculate a geographic midpoint (latitude and longitude) based on quantity shipped

Calculate a weighted average value

1. Drag the Weighted Average icon onto the design surface.
2.  On the Connections tab , enter a Name and a Description to identify this action, then specify the input and output data source option:

   Input -

   • For Database Connection, select the database containing the table to which the weighted average is being applied, or choose New Database Connection to establish a new connection to another database.
   • Table Name – Select the table containing the data.

   Output -

   • The Database Connection indicates the database of the modified table.
   • Enter or select a Table Name depending on the Output Mode you choose -
     • For Create/overwrite a table, enter the name of the table being created or overwritten. Using this mode, you can also choose to -
       • Add a new _autokey field to the table, with incremented values that make each table row unique. This is required by some database types.
       • Indicate that the table should be deleted after the macro or workflow has executed.
     • For Use an existing table, select the table to be used from the drop-down, and indicate whether the rows in the table should be deleted before the new data is written to the table.
3.  On the Configuration tab :
   • Drag the icon for each attribute field to be used for grouping from Table Fields into Group By Fields.
   • Drag the icon of each field with a characteristic to be averaged from Table Fields to Average Fields.
   • Drag the icon of the field to be used as the frequency of occurrence or order of magnitude that will be applied to the characteristics being averaged to the Weight Factor field.
   • In the Filter area, create an expression to more narrowly define the records to which the weighted average applies, open the Filter area and build the necessary expression by dragging icons from the Table Fields area. Expressions can be created directly in the Filter area, or you can use the Expression Editor.
   • In the Post Aggregation Filter area, create an expression to narrow the results of your summary to specific rows, such as rows with a total aggregate that exceeds a defined value. Expressions can be created directly in this area, or you can use the Expression Editor.
   • Click Preview to view the data or the SQL statement created from this action. Refer to Preview for information.
4. Click the Mapping tab to map the output fields to an existing table structure when using an existing table for the output. Refer to Field Mapping for more information.

Weighted Average data example