Summary calculations

Summary calculations are aggregate functions that are used for subtotals and totals. Summary calculations can be set in the Domain Designer or in the Ad Hoc view.

In Ad Hoc table views, each field displays a single summary calculation. The summary calculation is automatically applied to all groups in the table. Summaries appear at the end of each group, and at the end of the view. When a new group is added, it includes a summary for each column.

In Crosstabs, each measure displays a summarized value. Summaries determine the values of the Totals at the intersection of each row and column.

In Charts, the type of chart determines whether measures are summarized. If summaries are used, they determine the size or location of the graphical elements that represent your data.

For dual pie charts, the summary function for the field needs to be Sum or CountAll. Dual pie charts with other summary functions might give unexpected results. You can change the summary calculation of most measures.

By default, the fields of each data type are summarized as shown in the following table.
Table 1. Default summary functions in calculated fields
Default Summary Calculation Data type Description
Sum Numeric Displays the sum of all values in the set.
CountAll Date Displays the total number of values in the set.
CountAll String Displays the number of values in the set.
CountAll Boolean Displays the number of values in the set.
AggregateFormula Aggregate For a calculated field that uses an aggregate function, Aggregate types use the same aggregate formula as the summary.
None Combined For a calculated field that combines an aggregate function with a non-aggregate function, the summary calculation is null.
Select from the following options to set a measure’s summary function in any type of view.
Table 2. Summary function options
Function Meaning Available for
AggregateFormula For a calculation that uses an aggregate function, uses the same aggregate formula as the summary. Aggregate
Average Displays the average of all values in the set. Numeric
CountAll Displays the number of rows in the set.
  • Boolean
  • Date
  • Numeric
  • String
CountDistinct Displays the number of unique values in the set.
  • Boolean
  • Date
  • Numeric
  • String
Custom You can enter an aggregate calculation for the summary. The Custom summary option is only available for calculated fields and measures in the Ad Hoc Editor. It is not available for Domains.
  • Aggregate
  • Date
  • Numeric
Max Displays the highest value in the set. Date and Numeric
Median Displays the median value of the set. Date and Numeric
Min Displays the lowest value in the set. Date and Numeric
Mode Displays the value that occurs most frequently in the set.
  • Boolean
  • Date
  • Numeric
  • String
None The aggregate function is null. No summary function displays.
  • Aggregate
  • Boolean
  • Date
  • Numeric
  • String
Range Displays the difference between the minimum and maximum values of the set. Numeric
RangeDays Displays the difference in days between the minimum and maximum values of the set. Date
RangeHours Displays the difference in hours between the minimum and maximum values of the set. DateTime
RangeMinutes Displays the difference in minutes between the minimum and maximum values of the set. Displays the difference in minutes between the minimum and maximum values of the set. DateTime
RangeMonths Displays the difference in months between the minimum and maximum values of the set. Date
RangeQuarters Displays the difference in quarters between the minimum and maximum values of the set. Date
RangeSemis Displays the difference in semi-annual periods between the minimum and maximum values of the set. Date
RangeWeeks Displays the difference in weeks between the minimum and maximum values of the set Date
RangeYears Displays the difference in years between the minimum and maximum values of the set. Date
Aggregate Formula Uses the aggregate formula that is used to define the calculated field as the summary function and sets the appropriate level for the context. Aggregate
StdDevP Displays the standard deviation for the population of the set. Numeric
StdDevS Displays the standard deviation on a sample for the set. Numeric
Sum Displays the grand total for the set. Numeric
WeightedAverage Displays the weighted average for the set, based on a second numeric field or expression. Only available for calculated fields and measures in the Ad Hoc Editor, and not available for Domains. Numeric
Note: Be aware of the following points when you use summaries:
  • The calculation is shown in parentheses after the field name when you complete the following actions:
    • Choose a field.
    • Select a summary calculation other than the default.
  • In Ad Hoc views, the following outcomes can occur when you create a calculated field or measure with the following types of summary calculations:
    • If you create a custom summary calculation for a field or measure, Custom is available on the Change Summary Function menu for that field. It is not available otherwise.
    • If you create a WeightedAverage summary calculation for a field or measure, WeightedAverage is available on the Change Summary Function menu for that field. It is not available otherwise.
  • You can remove summaries by setting the summary function to None.
  • Only AggregateFormula, Custom, or None are supported as summary calculations for aggregate functions. The Custom summary option appears in the Change Summary menu only if you define a custom function in the Create Calculated Field dialog box.