Stats are common tools for processing and displaying data and the Aggregation function in Metrics of pivot tables is frequently used in statistics.

In order to acquire more accurate analysis results, you need to know the aggregation rules of pivot tables in detail.

1. When fields in a form are directly aggregated as the metrics of a pivot table, the aggregation rule is to calculate first and then aggregate.

2. When adding a calculated field as a metric and a Math function is nested in the formula, the aggregation rule is to aggregate first according to the function and then to aggregate (that is, aggregating before calculating). If there is no Math function nested in the formula, the aggregation rule is to calculate first and then aggregate (that is, calculating before aggregating).

Aggregation methods in Metric include:

1. When number fields of a form are used as metrics of the pivot table, the aggregation method supports Sum, AVG, Max, Min, Count, and Dedupe. (The default is Sum.)

2. When non-number fields of a form are used as metrics, the aggregation method supports Count/Dedupe. (The default is Count.)

3. When number fields of a subform are used as metrics, the aggregation method supports Sum, AVG, Max, Min, and Count. (The default is Sum.)

4. When non-number fields of a subform are used as metrics, the aggregation method only supports Count.

5. The aggregation rows/columns of the pivot table are aggregated according to the logic of metrics.

The calculation rules of calculating before aggregating and aggregating before calculating will be explained in detail.

Generally, the aggregation method of metrics that have not been operated by math functions is to calculate before aggregating. This is also the most common method of aggregation.

For example, to calculate the total sales, use "the sales volume * unit price" to calculate the sales volume of each order, and then set this field as a metric to sum and aggregate data. Finally, the total sales can be aggregated as follows:

The formula for the calculated field is as follows:

If calculated fields are added as metrics, such as a Math function nested in the formula, the aggregation method is to aggregate first according to the function and then to calculate (that is, aggregating before calculating).

1. Calculate the completion rate

For example, calculate the completion rate for each member. Since members enter the completion quantity every day, so when you calculate the completion rate, you need to sum the completion quantity in history and then calculate the completion rate.

The formula for calculated fields is as follows:

2. Daily completion quantity per capita

The number of daily completion quantity per capita is equal to that of the total completion volume for the day/that of people for the day. You need to use the SUM function to sum the daily completion quantity and then use the COUNT function to count the number of people. The formula of daily completion quantity per capita is SUM(Daily Completion Quantity)/COUNT(Employee Name).

The formula for calculated fields is as follows:

1. If the value of the field as a metric is empty in one record, this record will be filtered and not displayed.

2. When subfields are included in dimensions, the metric only supports subfields.

3. Multiple Multi Select fields cannot be used as dimensions at the same time.

4. Fields from different subforms cannot be aggregated.

5. When there are Multi Select fields of the form in the dimension, subfields cannot be set as metrics.

6. The total amount of pivot table access data is up to 16 MB.

7. When the data source is a form, data alert can only be set in charts when the data permission is All Form Data. Area charts, pie charts, and radar charts don't support data alert.

1. According to the content of aggregation logic and the expected aggregation result, check whether there is any problem in the setting of metrics and formulas;

2. In the data source form, check whether the value of the field as the metric is empty. The data will be filtered out and not aggregated.

I have finished reading. 🏆

👉I can't wait to Try for myself.

👉I need more HELP in Discord Forum.