The aggregate( function calculates an aggregate (sum (total), count, minimum, maximum or average) by scanning specified records in a database.
Parameters
This function has five parameters:formula – a formula which calculates the individual values that will be incorporated into the aggregate value. Usually this is simply a field name, but any formula can be used. The formula must be quoted (see Quotes).
aggregation – The specific method by which the values are to be aggregated. The options are:
"sum" (also "+" or "total")"count""minimum" (also "<" or "min")"maximum" (also ">" or "max")"average" (or "avg")queryformula – an optional formula that determines whether a record should be included in the aggregate calculation. For example, the query formula Price > 100 could be used if you only wanted to aggregate items with a price greater than 100. If this parameter is empty or omitted, all records will be aggregated. The formula must be quoted (see Quotes).
database – the database containing the values that will be aggregated. This database must be currently open. If this parameter is empty or omitted, the current database will be used.
includeallrecords – a Boolean value which determines whether all records should be aggregated. If this parameter is false, empty or omitted, only selected (visible) records will be aggregated. If this parameter is true, all records, including invisible records, will be aggregated.
Description
This function evaluates the specified formula for each record in the specified database, then aggregates the calculated values.
This example sums the numeric field Amount (in the current database).
aggregate({Amount})
This example sums the salaries in a personnel database, but only counts the first $100,000 of each person’s salary.
aggregate({min(Salary,100000)})
There are several different aggregation methods available.
aggregate({Salary},"minimum") ☞ returns the minimum salary of anyone on staff
aggregate({Salary},"maximum") ☞ returns the maximum salary of anyone on staff
aggregate({Salary},"average") ☞ returns the average salary of everyone on staff
If the count method is used, the formula parameter is ignored, and can be left empty. (This is more useful when combined with the queryformula parameter described below.)
aggregate({},"count") ☞ returns the number of visible records in the database
By adding a query formula, you can restrict the aggregate to a subset of the database. This example calculates the total amount of all records that are more than 30 days old.
aggregate({Amount},"+",{Date<today()-30}) ☞ total of all records that are more than 30 days old
aggregate({Salary},"average",{Department="Engineering"} ☞ average salary of engineers
aggregate({},"count",{Salary > 2 * aggregate("Salary","average")})
☞ number of staff members that make more than twice the average salary
Advanced Tip: The third example above may be slow if the database is large, since it recalculates the average over and over again for every record in the database. The example below shows how this can be done much faster. The first portion of the formula (the first line) calculates the average salary and stores it into a temporary variable named tempAverage (see the ignore( and cache( functions). The second part of the formula uses this temporary variable to count the number of employees that make more than twice the average salary.
ignore(0,cache(aggregate("Salary","average"),"tempAverage"))+
aggregate({},"count",{Salary > 2 * tempAverage})
So far all examples have calculated aggregates using data from the current database, but adding a fourth parameter allows a different database to be scanned. This example counts the number of invoices that are more than 30 days old. The Invoice database doesn’t have to be the currently active database (but it does have to be open).
aggregate({},"count",{Date<today()-30},"Invoices")
The previous examples have all summed selected (visible) records only. This next example sums ALL overdue invoices, whether they are selected or not.
aggregate({Total},"sum",{Date<today()-30},"Invoices",true())
To skip some optional parameters,(like queryformula and database) and use the last one, includeallrecords, true/false, use empty text for the optional parameters, either {} or "" as shown in this example.
aggregate({Amount},“total”,{},“”,false())
All of the aggregation options except average can be performed with a formula that generates text.
When used with text values, the sum aggregation appends all of the text values together. This is similar to the arraybuild( function but without the separator character.
Text values can also be aggregated using the minimum and maximum aggregations. This example returns the first surname (alphabetically) in the current database.
aggregate({lastword(Name)},"minimum")
Attempting to average text values will return an error.
PanoramaX by default displays the output of numerical operations on floating point numbers to fifteen significant digits. If you need fewer digits, use a pattern. For example, if the output of aggregating dollar amounts were 12218.6666666667, and you want to display that to the nearest penny, enclose the aggregated function in a pattern, such as
pattern(aggregate({Average},"average")),"#.##")
Date values can be aggregated using the minimum, maximum and average aggregations. This example calculates the number of days it has been since there was a sale over $1,000 dollars.
today() - aggregate({Date},"maximum",{Total > 1000},"Invoice")
When using the minimum or average aggregations with date values you should make sure that there are no empty values (empty values are treated as January 1, 4713 B.C.). This example finds the employee with the earliest hire date in the marketing department, and will work even if the hire date is missing for some employees (of course it won’t return any of the employees with missing dates as the earliest hire).
aggregate({HireDate},"minimum",{Department = "Marketing" and HireDate > 0})
If you specify a query formula that doesn’t match any records, the minimum value will be returned by all aggregations. If the formula specifies a numeric value, the minimum value is 0. If the formula specifies a text value the minimum is "".
Sometimes you may want to reference fields in the current database in the query formula. For example, suppose you have an Invoice database that contains customer names in a Name field, and a Customer database that contains First and Last name fields. This formula can be used in the Customer database to calculate the total of all invoices for the person currently selected in the Customer database.
aggregate({Total},"+",{Name = ««First»»+" "+««Last»»},"Invoices",true())
Notice that the First and Last fields are enclosed in double chevrons. The use of double chevrons specifies that a field is in the current database, instead of the database that is being aggregated. There are three fields referenced in this formula:
Name ☞ in Invoice database
First ☞ in Customer database (because of double chevrons)
Last ☞ in Customer database (because of double chevrons)
Advanced Note: Internally, Panorama actually converts double chevrons into the fieldvalue( function. So the example above is internally converted into this:
aggregate({Total},"+",
{Name = fieldvalue("Customer",First)+" "+fieldvalue("Customer",Last)},"Invoices",true())
The double chevron notation is a convenience, making this formula much easier to write, and to understand.
See Also
History
| Version | Status | Notes |
| 10.0 | New | New in this version. |