1. Modeling
  2. Line items
  3. Summary methods

Summary methods define how the total of child cells is calculated into one value. 

For example, if you want to calculate the data in 10 cells, you can sum them, find their average, or find the maximum value.

Each data type has a default summary method. For example, Sum is the default for the number data type, and None (no summary) is the default for the Boolean data type.

When you select a summary method, you're potentially calculating the totals of many cells. If a module contains many dimensions or deep hierarchies, totals can cause a significant increase in the overall number of cells.

It's best practice to set your line item's summary method to None, and only use another summary method when you need it. This approach ensures that aggregated cells are only calculated when you require them.

When you have chosen an appropriate summary method for your data, consider whether the summary is a strict sum of the data for all the members in the hierarchy. 

For example, you might want to add up units sold by stores in each region. In this case, you would select Sum, the default method. If you want to calculate the price of the units over time, you might select Average.

You can select a different summary method for the Time dimension to the summary method that you select for other dimensions.

Summary method descriptions

The table below describes the valid summary methods you can use with each data type.  

Summary methodData typeDescription
SumNumber

Adds values over time and other hierarchies. Sum is the default option for Number.

For example, for a Salary line item, the Sum method sums up the monthly salaries into a quarterly sum.

 2250 + 2250 + 2250 = 6750

NoneNumber, Date, List, Time Period, Text, BooleanTurns off summaries. Use this option to prevent the calculation of totals until you need them. 
FormulaNumber, Date, List, Time Period, Text, BooleanThe formula is applied by picking values from the referenced line items at the appropriate levels. Use formula when the line item that you reference in the formula is in a different module.
AverageNumber

Calculates the mean average for values over time and other hierarchies.  

See Average example below.

RatioNumber

Uses a ratio of two specified line items for the totals.

When you select Ratio, you must specify the line items used in the ratio. For example:

Ratio = Sales Revenue / Volumes

MinNumber, Date, Time PeriodUses the smallest value for totals. Infinity is used for any parent items that have no child items or descendants, except for dates where 31/12/2399 is used.
MaxNumber, Date, Time PeriodUses the largest value for totals. -Infinity is returned for any parent items that have no child items or descendants, except for dates where 1/1/1900 is used.

Opening Balance (Time summary only)

 

NumberUses the value from the first summarized time period for totals. For example, you might want to know how much cash was available at the start of a quarter.
Closing Balance (Time summary only)NumberUses the value from the last summarized time period for totals. You could use Closing Balance in a Headcount line item to show the headcount at the end of each quarter and year.
AllBooleanTotals are marked as true only if all of its range of cells are marked as true.
AnyBooleanTotals are marked as true if any of its range of cells are marked as true.
First non-blankDate, List, Time Period, TextThe first cell that contains data is used for the totals.
Last non-blankDate, List, Time Period, TextThe last cell that contains data is used for the totals.

Average example

In this example, the Average summary method calculates the average values for the Spain, France, and Europe line item totals. 

Spain and France averages are calculated by summing up the Unit price values for each city, and dividing them by the number of cities in each country. The Europe average is calculated by summing up the Unit price values for all European cities, and dividing them by the total number of cities.


Unit price
Madrid3.96
Barcelona4.40
Seville4.84
Spain4.40
Paris5.28
Lyon5.76
Bordeaux4.80
Marseille6.24
France5.52
Europe5.04