a!grouping( field, interval, alias, formatValue )
Determines the fields to group by in a query or chart that uses a record type as the source. The grouping should incorporate a record field or a related record field, an alias, and an optional interval to group by a date.
This configuration is used when a record type is defined as the data source for a chart or when using a!queryRecordType()
. This function can be used as a primary grouping or secondary grouping in a bar, column, line, or pie chart or to define the fields in a query using a!aggregationFields()
.
The a!grouping()
function must be used inside one of the following:
a!barChartConfig()
a!columnChartConfig()
a!lineChartConfig()
a!pieChartConfig()
a!aggregationFields()
See also:
Name | Keyword | Types | Description |
---|---|---|---|
Field |
|
Any Type |
The record field or related record field to group by. Use the |
Interval |
|
Text |
The date or time interval to group by. This parameter can only be used when the field is of type Date, Date and Time, or Time. Valid values: |
Alias |
|
Text |
The short name by which the result of the grouping field can be referenced in other places in the chart configuration or |
Format Value |
|
Any Type |
Display value for the given grouping value. Only applies when the grouping is used in a chart. This parameter is evaluated once for each value returned. You can reference the unformatted result from the data source using |
The interval
determines how to group data for fields of type Date, Date and Time, or Time. Each interval can combine data from multiple dates for easy reporting. For example, suppose you have three records with the following values for the startDate
field: March 17, 2021
; March 28, 2021
, and April 3, 2021
. If you use the MONTH_TEXT
interval, it will group the data together and return the values as March 2021
and April 2021
.
In addition to grouping the data, the interval can also provide localized date formats in some cases. For example, the MONTH_OF_YEAR
interval returns the month number, a value 1-12. The MONTH_OF_YEAR_TEXT
interval also groups the data by month, but it returns the text value of the month, such as January
. The value returned for the interval will vary based on the locale of the user who runs the query. If you need to ensure the value is the same across all locales (for example, to use the result in a filter), use one of the intervals that does not contain the word TEXT
.
By default, the AUTO
interval is selected. This interval does not perform any additional grouping, so the original value will be returned.
See the table below for the results using each interval:
Grouping Interval | Valid Data Types | Return Type | Localized? | Sample Value | Sample Result |
---|---|---|---|---|---|
AUTO |
Any Type | Any Type | No | 10/17/2021 3:45:12 PM | 10/17/2021 3:45:12 PM |
YEAR |
Date and Time; Date | Integer | No | 10/17/2021 | 2021 |
MONTH_OF_YEAR |
Date and Time; Date | Integer | No | 10/17/2021 | 10 |
MONTH_OF_YEAR_SHORT_TEXT |
Date and Time; Date | Text | Yes | 10/17/2021 | Oct |
MONTH_OF_YEAR_TEXT |
Date and Time; Date | Text | Yes | 10/17/2021 | October |
MONTH_DATE |
Date and Time; Date | Integer | No | 10/17/2021 | 10/1/2021 |
MONTH_SHORT_TEXT |
Date and Time; Date | Text | Yes | 10/17/2021 | Oct 2021 |
MONTH_TEXT |
Date and Time; Date | Text | Yes | 10/17/2021 | October 2021 |
DATE |
Date and Time; Date | Date | No | 10/17/2021 3:45 PM | 10/17/2021 |
DATE_SHORT_TEXT |
Date and Time; Date | Text | Yes | 10/17/2021 3:45 PM | Oct 17, 2021 |
DATE_TEXT |
Date and Time; Date | Text | Yes | 10/17/2021 3:45 PM | October 17, 2021 |
DAY_OF_MONTH |
Date and Time; Date | Integer | No | 10/17/2021 3:45 PM | 17 |
HOUR_OF_DAY |
Date and Time; Time | Integer | No | 10/17/2021 3:45 PM | 15 |
HOUR |
Date and Time | Date and Time | No | 10/17/2021 3:45 PM | 10/17/2021 3:00 PM |
MINUTE_OF_HOUR |
Date and Time; Time | Integer | No | 10/17/2021 3:45:12 PM | 45 |
MINUTE |
Date and Time | Date and Time | No | 10/17/2021 3:45:12 PM | 10/17/2021 3:45:00 PM |
field
is a required parameter. It also must reference a record field or a related record field from the record type specified in the query or chart.sort
parameter within the parent configuration function. When using a!grouping
in a query, an alias is required.formatValue
, the expression provided will execute for each unique value returned for the grouping. See Chart Configuration Using Records for more information on how to set up the format value and some examples.The following patterns include usage of the Grouping Component.
Aggregate Data and Conditionally Display in a Chart or Grid (Reports, Charts, Query Data, Grids): Aggregate data and conditionally display it in a pie chart or grid. In this pattern, we will calculate the total number of employees in each department and display it in a pie chart and a read-only grid. Then, we'll use a link field to conditionally display each component.
Aggregate Data by Multiple Fields and Display in a Chart (Reports, Charts, Query Data): Aggregate data by multiple fields and display it in a stacked column chart.
Aggregate Data on a Date or Date and Time Field (Reports, Query Data, Grids): Aggregate the total number of employees by date and display it in a bar chart.
Configure a Chart Drilldown to a Grid (Charts, Grids, Query Data): Displays a column chart with aggregate data from a record type and conditionally shows a grid with filtered records when a user selects a column on the chart.
Filter the Data in a Grid Using a Chart (Charts, Grids, Filtering): Display an interactive pie chart with selectable sections so that a user may filter the results in a grid.