Statistic
Select by comparing the source field value using the given Condition to other values in the table.
Condition options:
- maximum - selects records with the maximum value,
- minimum - selects records with the minimum value,
- median - selects records with the median value,
- top - selects the specified number of records with the largest values,
- bottom - selects the specified number of records with the smallest values,
- top percent - selects records with values larger than the specified percentile, with an option to include or exclude the bounding percentage.
- bottom percent - selects records with values smaller than the specified percentile, with an option to include or exclude the bounding percentage.
Other options:
- Ignore time - Ignore time when comparing values. Compare only dates. Checked by default.
For examples of how the above option can affect lexicographical sort order, see the Select - Datetime: Unique topic.
If we would like to select records that are logical NOT variations of the above, for example not maximum, select on the basis of one of the above and press Ctrl-I or Edit - Select Inverse to invert the selection. Selecting using maximum and then pressing Ctrl-I is the equivalent of "not maximum".
Launch the template by choosing a numeric field in the Select pane and then double-clicking the Statistic template. When the template launches we can specify options.
Statistic : maximum
Select records with the maximum value in the table.
We start with a table with a single Datetimes field of type datetime. Some of the values are the same for both date and time, and some of the values have the same date but a different time. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Select pane we choose the Datetimes field and then we double-click the Statistic template to launch it.
In the Statistic template we choose maximum as the Condition option. We leave the Ignore time box checked, the default. For the Action, we use the default replace selection option.
Press Select.
In the illustration above, we have Ctrl-clicked the column header for the Datetimes field, to sort the field from lowest value to highest value.
Records with an Datetimes field value in the date part of the field of 01/01/2020 the maximum value that occurs in that field in the table, are selected. In this table, there are two records that have the maximum value. When the Ignore time box is checked, the different times for those records are ignored. If the Ignore time box had been unchecked, only the record with the later time would have been selected.
Statistic : minimum
Select records with the minimum value in the table.
We start with a table with a single Datetimes field of type datetime. Some of the values are the same for both date and time, and some of the values have the same date but a different time. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Select pane we choose the Datetimes field and then we double-click the Statistic template to launch it.
In the Statistic template we choose minimum as the Condition option. We leave the Ignore time box checked, the default. For the Action, we use the default replace selection option.
Press Select.
In the illustration above, we have Ctrl-clicked the column header for the Datetimes field, to sort the field from lowest value to highest value.
Records with an Datetimes field value in the date of 01/26/1975, the minimum value that occurs in that field in the table, are selected. In this table, there is only one record that has the minimum value. When the Ignore time box is checked, only the date part of the date time field is considered and the time part is ignored.
Statistic : median
Select records with the median value in the table.
We start with a table with a single Datetimes field of type datetime. Some of the values are the same for both date and time, and some of the values have the same date but a different time. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Select pane we choose the Datetimes field and then we double-click the Statistic template to launch it.
In the Statistic template we choose median as the Condition option. We leave the Ignore time box checked, the default. For the Action, we use the default replace selection option.
Press Select.
In the illustration above, we have Ctrl-clicked the column header for the Datetimes field, to sort the field from lowest value to highest value.
Records with an Datetimes field value of the date of 12/25/2009, the median value that occurs in that field in the table, are selected. When the Ignore time box is checked, only the date part of the date time field is considered and the time part is ignored.
Statistic : top
Select the specified number of records with the largest values.
We start with a table with a single Datetimes field of type datetime. Some of the values are the same for both date and time, and some of the values have the same date but a different time. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Select pane we choose the Datetimes field and then we double-click the Statistic template to launch it.
In the Statistic template we choose top as the Condition option. For the Records number, we enter 5. We leave the Ignore time box checked, the default. For the Action, we use the default replace selection option.
Press Select.
In the illustration above, we have Ctrl-clicked the column header for the Datetimes field, to sort the field from lowest value to highest value.
The 5 records with the highest values in the table in their Datetimes fields are selected. Two records near the top of the table, the fifth and sixth in order, have the same value, so to return only five records the template has to pick only one of them.
When the Ignore time box is checked, only the date part of the date time field is considered and the time part is ignored.
Statistic : bottom
Select the specified number of records with the smallest values.
We start with a table with a single Datetimes field of type datetime. Some of the values are the same for both date and time, and some of the values have the same date but a different time. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Select pane we choose the Datetimes field and then we double-click the Statistic template to launch it.
In the Statistic template we choose bottom as the Condition option. For the Records number, we enter 5. We leave the Ignore time box checked, the default. For the Action, we use the default replace selection option.
Press Select.
In the illustration above, we have Ctrl-clicked the column header for the Datetimes field, to sort the field from lowest value to highest value.
The 5 records with the lowest values in the table in their Datetimes fields are selected. Two of those records have the same value in the date part, 04/01/2006.
When the Ignore time box is checked, only the date part of the date time field is considered and the time part is ignored.
Statistic : top percent
Select records with values larger than the specified percentile, with an option to include or exclude the bounding percentage.
We start with a table with a single Datetimes field of type datetime. Some of the values are the same for both date and time, and some of the values have the same date but a different time. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Select pane we choose the Datetimes field and then we double-click the Statistic template to launch it.
In the Statistic template we choose top percent as the Condition option. For the Percent number, we enter 25. We leave the Ignore time box checked, the default.
We uncheck the Include bound box.
For the Action, we use the default replace selection option.
Press Select.
In the illustration above, we have Ctrl-clicked the column header for the Datetimes field, to sort the field from lowest value to highest value.
In this table, there are 14 records, so 25 percent of those records would be 3.5 records if the values within the records were evenly distributed. However, the values are not evenly distributed. Two of the records have the same value in the date part, 01/01/2020, the maximum value in the table. When the Ignore time box is checked, only the date part of the date time field is considered and the time part is ignored.
With the Include bound box unchecked, the template selects the 3 records with top values. Records with a date value of 09/02/2018 or later are in percentiles lower than the 25th percentile. Records with a date value of 12/03/2015 or earlier are not selected, because to get those we have to include the 25th percentile.
We can see how the selection would be different with the Include bound box checked.
We check the Include bound box. Press Select.
This time, the record containing the 12/03/2015 date value also is selected. Five records are selected because the values in records are not evenly distributed in the 14 record population of the table.
See additional examples of how variation in table population can affect selection by top and bottom percentiles in the Select - Numbers: Statistic topic.
Statistic : bottom percent
Select records with values smaller than the specified percentile, with an option to include or exclude the bounding percentage.
We start with a table with a single Datetimes field of type datetime. Some of the values are the same for both date and time, and some of the values have the same date but a different time. We have used the Layers pane to hide the mfd_id field, for a simpler illustration.
With the focus on the table window, in the Select pane we choose the Datetimes field and then we double-click the Statistic template to launch it.
In the Statistic template we choose bottom percent as the Condition option. For the Percent number, we enter 25. We leave the Ignore time box checked, the default.
We uncheck the Include bound box.
For the Action, we use the default replace selection option.
Press Select.
In the illustration above, we have Ctrl-clicked the column header for the Datetimes field, to sort the field from lowest value to highest value.
The three records with a date value of 12/28/2004 in the Datetimes field are selected. That is because the only group of records that fall into less than the 25th percentile of least values are those records. If the three records with a date value of 04/01/2006 were included, those would fall into the 25th percentile, for which we would have to check the Include bound box.
We can see how the selection would be different with the Include bound box checked.
We check the Include bound box. Press Select.
This time, the three records containing a date value of 04/01/2006 are also selected. Six records are selected because the values in records are not evenly distributed in the 14 record population of the table.
When the Ignore time box is checked, only the date part of the date time field is considered and the time part is ignored.
If we uncheck the Ignore time box, that will make a difference, since two of the three records containing a date value of 04/01/2006 have the same time, while one of them has an earlier time.
We uncheck the Ignore time box and then we press Select.
Now, only four records are selected, because all of the records with a date value of 04/01/2006 are not considered identical. The two records that have a date value of 04/01/2006 but which have later times, now fall outside the 25th percentile when the time part of the datetime value is also considered.
See additional examples of how variation in table population can affect selection by top and bottom percentiles in the Select - Numbers: Statistic topic.