Summary Modes for Tables

Version

Application Scenario

You can set the summary method for indicator fields of the table, that is to say, you can quickly get calculation results of frequently used functions through clicking.

Function Description

Summary Mode includes Sum, Average, Median, Maximum, Minimum, Percentile, Standard Deviation, and Variance, as shown in the following figure.

Summary Mode can only be used in Group Table and Cross Table.

Note:

To calculate the indicator, you need to first summarize the indicator according to Summary Mode and then calculate the indicator according to the function you have set.

Sample data: Contract Information.xlsx

The default Summary Mode for indicator fields is Sum, which is used to summarize the indicators grouped by the dimension fields.

For example, the Contract Amount column displays the total contract amount for different contract types.

Average is used to calculate the average of the indicator grouped by the dimension fields. For better understanding, drag Contract Amount, Number of Records, and Contract Amount into the indicator bar. Select Average from the drop-down list for the second Contract Amount field.

Contract Amount (AVERAGE) = Contract Amount (SUM)/Number of Records. The result is the average contract amount for each contract type, as shown in the following figure.

Median is used to get the middle number in a sorted list of all values of an indicator, grouped by dimension fields. Sort the contracts of Service Agreement in descending order according to Contract Amount. The median is 180,000.

The median enables you to observe the characteristics of the middle data point for clearer data distribution display.

Group the indicator fields according to dimension fields and then calculate the maximum/minimum value within each group.

For example, the maximum amount of the contracts of the Service Agreement type is 2,200,000 and the minimum is 10,000.

Percentile is used to the data corresponding to each value percentile after the indicator fields are grouped by dimension fields.

For example, you can calculate the data in the quartile position and the data in the median position.

Note for Direct-Connected Data

Database supporting direct-connected data: ClickHouse,PivotalGreenplum Database,Oracle ,Postgresql （9.4 or higher versions）,REDSHIFT、PRESTO,sybase IQ,VERTICA, and Alibaba Cloud MaxCompute

Note:

When the database system is REDSHIFT, you can only perform the aggregation operation of the same field that needs to be sorted (for example, to calculate Distinct Count, Median, Percentile, and approximate Distinct Count). An error will occur if you perform such an aggregation operation on two or more fields at the same time.

Example

You need to analyze the battery quality by filtering the value of Remaining Power with the percentile excessing 95%. The original data is shown in the following figure.

Group Battery Type by dimension and calculate the value at the 95% percentile for each type of battery, as shown in the following figure.

Take the square root of the variance of the indicator fields grouped by the dimension fields to calculate Standard Deviation.

Variance and Standard Deviation are used to measure dispersion. If you want to know whether there is a big difference in Contract Amount per order between different Contract Type, you can use Variance and Standard Deviation. The standard deviation of Purchase Contract is the largest, indicating that data in this group are relatively scattered, namely, the difference of Contract Amount is large.

Variance is the mean of the squared differences between each value and the overall mean, after grouping by dimension fields.