Difference between revisions of "Aggregate Functions"

From AgileApps Support Wiki
imported>Aeric
(Created page with "<h3> Aggregate Functions</h3> <div style="font-family: Arial, Verdana, sans-serif; font-size: 12px; color: rgb(34, 34, 34); background-color: rgb(255, 255, 255); "> <p> <strong>B…")
Β 
imported>Aeric
Β 
(50 intermediate revisions by the same user not shown)
Line 1: Line 1:
<h3>
<noinclude>__NOINDEX__</noinclude>
Aggregate Functions</h3>
__TOC__
<div style="font-family: Arial, Verdana, sans-serif; font-size: 12px; color: rgb(34, 34, 34); background-color: rgb(255, 255, 255); ">
====About the Aggregate Functions====
<p>
Thee ''aggregate functions'' let you select a group of records from an arbitrary object and produce an aggregate value from them, without having to write Java code. The aggregate value can be sum of values, an average, maximum, or minimum value, or it can be a simple count of records.
<strong>Background</strong></p>
<p>
Spreadsheet users are used to functions like SUM and COUNT that operate on a range of data.<br />
This enhancement provides similar functionality, in a way that lets the designer specify the records to operate on.</p>
<p>
<strong>Overview</strong></p>
<p>
Formula functions provide the capability for selecting a group of records from an object and<br />
producing an aggregate value (SUM, COUNT, MAX, MIN, AVG), without having to write Java code.</p>
<p>
<strong>Functionality</strong></p>
<table border="1" cellpadding="1" cellspacing="1" style="width: 457px; height: 111px; ">
<tbody>
<tr>
<td>
<strong>Methods</strong></td>
<td>
<strong>Field Types</strong></td>
</tr>
<tr>
<td>
SUM(<em>object_id, object_field, criteria</em>)</td>
<td>
number, currency, boolean</td>
</tr>
<tr>
<td>
MAXIMUM(<em>object_id, object_field, criteria</em>)</td>
<td>
number, currency, date</td>
</tr>
<tr>
<td>
MINIMUM(<em>object_id, object_field, criteria</em>)</td>
<td>
number, currency, date</td>
</tr>
<tr>
<td>
AVG(<em>object_id, object_field, criteria</em>)</td>
<td>
number, currency, boolean</td>
</tr>
<tr>
<td>
COUNT(<em>object_id, criteria</em>)</td>
<td>
n/a</td>
</tr>
</tbody>
</table>
<p>
where:</p>
<p style="margin-left: 40px; ">
<strong>object_id</strong>:<em>&nbsp;</em>Object Name or Object Identifier</p>
<p style="margin-left: 40px; ">
<strong>object_field: &nbsp;</strong>Field in the specified object&nbsp;<br />
<em>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<strong>Restriction:&nbsp;</strong></em>Fields in A Lookup field&nbsp;<em>cannot</em>&nbsp;be used to reference a field in a related record<em>.</em></p>
<p style="margin-left: 40px; ">
<strong>criteria:&nbsp;&nbsp;</strong>A condition that specifies the object records that are part of the collection.</p>
<ul>
<li style="margin-left: 40px;">
Syntax is same as searchRecords() API of JAVA/REST criteria<br />
The relevant information is here (only the page title is inappropriate):<br />
<span style="color: rgb(178, 34, 34);">&nbsp;</span><a href="http://lj.platformatyourservice.com/wiki/JAVA_API:Filter_Expressions_in_JAVA_APIs">http://lj.platformatyourservice.com/wiki/JAVA_API:Filter_Expressions_in_JAVA_APIs</a></li>
<li style="margin-left: 40px;">
Criteria must contain at least one index field. If index is combination of fields, the criteria must contain first field in that index.<br />
&nbsp;&nbsp;&nbsp; - It need not be a unique index.<br />
&nbsp;&nbsp;&nbsp; - This is a performance requirement, to provide for reasonably efficient accessing of records.</li>
<li style="margin-left: 40px;">
<strong>Note:</strong><br />
The maximum number indexes allowed in an index definition is currently 5.</li>
<li style="margin-left: 40px;">
Using index field in criteria will be validated in evaluation time also.</li>
<li style="margin-left: 40px;">
Index can be edited/removed after creation of the formula definition, for that reason formula validation for index presence occurs at runtime also.<br />
&nbsp;</li>
</ul>
<p style="margin-left: 80px; ">
e.g <strong>&#39;price &lt; 50&#39;</strong></p>
<p style="margin-left: 40px; ">
<em><strong>Notes:</strong></em></p>
<ul>
<li style="margin-left: 40px; ">
The SUM() of a boolean field gives a count of values that are yes/true.</li>
<li style="margin-left: 40px; ">
The AVG() of a boolean field gives the percentage that are yes/true.</li>
<li style="margin-left: 40px; ">
<em>The result does&nbsp;not&nbsp;take into account user-security restrictions.<br />
It depends on the total number of records, irrespective of user capabilities.</em><br />
(Document this fact prominently.)</li>
</ul>
====Availability====


The Aggregate Functions are available here:</p>
{{Tip|A [[Rollup Summary Field]] is often more useful than an aggregate function. With a Rollup Summary Field you define the same kinds of summary calculations for a set of ''related'' records--for example, to sum the line items in an order--and store that value as a field in the record, where it is kept up to date whenever the set of related records is modified.}}
:* Validations
:* Data Policies
::* Criteria (Action Based - Add, Updae, Delete, Import Record, Ownership Change, Workflow State Change)
::* Add/Update Record Action
:* Calendar Data Policies - Criteria Not Support</li>
:* Calendar Data Policies - Add/Update Record Actions Aggregate functions will be evaluated.</li>
:* Default Values
:* Formula Fields


They are not available here:
The aggregate functions are available for use in:
:* Layout Rules
:* [[Default Field Values]]
:* Custom Access Criteria
:* [[External Lookup#Lookup Service Configuration]]
:* Reports
:* [[External Lookup#Search Service Configuration]]
:* [[Layout Rules#Conditions and Actions|Layout Rule Conditions]]
:* [[Layout Rules#Actions|Layout Rule assign-value-to-field action]]
:* [[Formula Fields]]
:* [[Validations|Validation Criteria]]
Β 
They are not available for use in other contexts in which a [[Formula Expression]] may be created.
Β 
<span id="SUM"></span> <span id="AVG"></span>
<span id="MAX"></span> <span id="MIN"></span> <span id="COUNT"></span>
Β 
====The Aggregate Functions: SUM, AVG, MAXIMUM, MINIMUM, COUNT====
:{| border="1" cellpadding="5" cellspacing="1"
! Methods !! Field Types
|-
| <tt>SUM('objectName', 'fieldName', 'criteria_string')</tt> || number, currency, boolean
|-
| <tt>AVG('objectName', 'fieldName', 'criteria_string')</tt> || number, currency, boolean
|-
| <tt>MAXIMUM('objectName', 'fieldName', 'criteria_string')</tt> || number, currency
|-
| <tt>MINIMUM('objectName', 'fieldName', 'criteria_string')</tt> || number, currency
|-
| <tt>COUNT('objectName', 'criteria_string')</tt> || n/a
|}
where:
:* ''''objectName' '''
::: A string containing the object name (not its display label) or the object ID.
::: For example: <tt>'Some_Object'</tt>
:* ''''fieldName' '''
::: A string containing the name of a field in the specified object.
::: Must be the field name, not its display label. For example: <tt>'some_field'</tt>.
::: Must be a simple field of type number, currency, or boolean.
::: Does not work for formula fields, text fields, Lookups, or any other kind of field.
:* ''''criteria_string''''
::: A condition that specifies the object records that are part of the collection, ''where the expression is contained in a string''.
::: Typically, the criteria selects some field in the object record and compares it to a value in the current record. For example, to get total Orders for different sections of the country, you might have a Section object that contains one record for each state. (That arrangement would let you shift a state from one section to another, at will.) You might then use an aggregate function to total orders for each record:
:::: <tt>SUM('Order_Items', 'item_total', 'customer_state =' + section_state)</tt>
::: You could then write a report to group the records by section name, giving you totals for each section of the country.
Β 
{{Important|The results do not take into account user-security restrictions. These functions operate on the entire set of records in an [[Object]], regardless of user capabilities.}}
Β 
====Considerations====
:* The SUM() of a boolean field gives a count of values that are yes/true.
:* The AVG() of a boolean field gives the percentage that are yes/true.
:* The criteria expression must contain at least one index field.
:* If the index is a combination of fields, the criteria expression must contain the first field in that index. (It does not need to be a unique index, but an index is required, for performance.)
::''Note:'' An object can have up to five indexes.
:* Unlike criteria specified elsewhere in the platform, the criteria expression provided here is in the form of a string.<br>Therefore:
:# To test a boolean field, concatenate the value to a string--because in Java, anything concatenated to a string is converted to a string.
:#:For example: <tt><nowiki>'' + boolean_field</nowiki></tt>
:#:or: <tt>boolean_field + ' AND customer_state = ' + section_state</tt>
:# To include a string literal in a criteria string, use two single-quotes for the literal, and enclose the whole expression in single quotes. (The doubling is an ''escape'' that produces a single quote within the string.)
:#:For example: <tt><nowiki>'string_field = ''some value'' AND customer_state = ' + section_state</nowiki></tt>
::: To learn about other operators you can use in expressions, see [[Common:Filter Expressions in APIs|Filter Expressions in APIs]].<br>(But keep the principles above in mind.)

Latest revision as of 10:06, 24 October 2019

About the Aggregate Functions

Thee aggregate functions let you select a group of records from an arbitrary object and produce an aggregate value from them, without having to write Java code. The aggregate value can be sum of values, an average, maximum, or minimum value, or it can be a simple count of records.

Thumbsup.gif

Tip: A Rollup Summary Field is often more useful than an aggregate function. With a Rollup Summary Field you define the same kinds of summary calculations for a set of related records--for example, to sum the line items in an order--and store that value as a field in the record, where it is kept up to date whenever the set of related records is modified.

The aggregate functions are available for use in:

They are not available for use in other contexts in which a Formula Expression may be created.

The Aggregate Functions: SUM, AVG, MAXIMUM, MINIMUM, COUNT

Methods Field Types
SUM('objectName', 'fieldName', 'criteria_string') number, currency, boolean
AVG('objectName', 'fieldName', 'criteria_string') number, currency, boolean
MAXIMUM('objectName', 'fieldName', 'criteria_string') number, currency
MINIMUM('objectName', 'fieldName', 'criteria_string') number, currency
COUNT('objectName', 'criteria_string') n/a

where:

  • 'objectName'
A string containing the object name (not its display label) or the object ID.
For example: 'Some_Object'
  • 'fieldName'
A string containing the name of a field in the specified object.
Must be the field name, not its display label. For example: 'some_field'.
Must be a simple field of type number, currency, or boolean.
Does not work for formula fields, text fields, Lookups, or any other kind of field.
  • 'criteria_string'
A condition that specifies the object records that are part of the collection, where the expression is contained in a string.
Typically, the criteria selects some field in the object record and compares it to a value in the current record. For example, to get total Orders for different sections of the country, you might have a Section object that contains one record for each state. (That arrangement would let you shift a state from one section to another, at will.) You might then use an aggregate function to total orders for each record:
SUM('Order_Items', 'item_total', 'customer_state =' + section_state)
You could then write a report to group the records by section name, giving you totals for each section of the country.

Warn.png

Important: The results do not take into account user-security restrictions. These functions operate on the entire set of records in an Object, regardless of user capabilities.

Considerations

  • The SUM() of a boolean field gives a count of values that are yes/true.
  • The AVG() of a boolean field gives the percentage that are yes/true.
  • The criteria expression must contain at least one index field.
  • If the index is a combination of fields, the criteria expression must contain the first field in that index. (It does not need to be a unique index, but an index is required, for performance.)
Note: An object can have up to five indexes.
  • Unlike criteria specified elsewhere in the platform, the criteria expression provided here is in the form of a string.
    Therefore:
  1. To test a boolean field, concatenate the value to a string--because in Java, anything concatenated to a string is converted to a string.
    For example: '' + boolean_field
    or: boolean_field + ' AND customer_state = ' + section_state
  2. To include a string literal in a criteria string, use two single-quotes for the literal, and enclose the whole expression in single quotes. (The doubling is an escape that produces a single quote within the string.)
    For example: 'string_field = ''some value'' AND customer_state = ' + section_state
To learn about other operators you can use in expressions, see Filter Expressions in APIs.
(But keep the principles above in mind.)