Operations
The calculations available are:
- Average
- For each group, calculates the average value of a given field. For example, if you had a group of records with values 10, 12, 1, and 600 in a given field, the average value of that field for that group would be 155.75, calculated as (10+12+1+600)รท4.
- Maximum
- For each group, returns the largest value in a given field. For example, if you had a group of records with values 10, 12, 1, and 600 in a given field, the maximum value of that field for that group would be 600.
- Minimum
- For each group, returns the smallest value in a given field. For example, if you had a group of records with values 10, 12, 1, and 600 in a given field, the minimum value of that field for that group would be 1.
- Percent Rank
- For each record within a group, calculates the percentile rank of a value in a given field relative to other records in the group. The percentile rank represents the percentage of records in the group with lower values in the field.
- Percentile
- For each group, calculates the value that would represent the percentile you specify (0 - 100) for a given field. A percentile represents the percentage of records that have a lower score. For example, if you have a group of records with values 22, 26, and 74, and you perform a percentile calculation specifying the 60th percentile, the operation would return 35.6. This means that a record with a value of 35.6 in the given field would be in the 60th percentile of records in the group.
- Standard Deviation
- For each group, calculates the standard deviation for a given field. The standard deviation measures the amount of dispersion within the group. The lower the standard deviation, the more the values are centered around the mean value, and therefore the less dispersed the values. The higher the value, the more widely dispersed the values. The standard deviation is expressed in the same units as the data. The standard deviation is the square root of the variance.
- Sum
- For each group, calculates the sum of the values for a given field.
- Variance
- For each group, calculates the variance for a given field. The variance measures the amount of dispersion within the group. It is the square of the standard deviation.
- ZScore
- For each record in a group, returns the ZScore. The ZScore indicates how many standard deviations a value is above or below the group's mean.
- Alphabetical First
- For each group, returns first dictionary value. If there are more than one field values having same length or dictionary position, it returns the first occurrence of that value. For example, if group has a record with values Joel and Joey in a field, then the alphabetical first value for a group will be Joel as l comes before y in alphabet.
- Alphabetical Last
- For each group, returns last dictionary value. If there are more than one field values having same length or dictionary position, it returns the last occurrence of that value. For example, if group has a record with values Joel and Joey in a field, then the alphabetical last value for a group will be Joey as y comes after l in alphabet.
- Longest
- For each group, returns longest value. For example, if group has a record with values Joel and Jacob in a field, then the longest length value for a group will be Jacob as it has 5 alphabets whereas Joel has 4.
- Shortest
- For each group, returns shortest value. For example, if group has a record with values Joel and Jacob in a field, then the shortest length value for a group will be Joel as it has 4 alphabets whereas Jacob has 5.
- Latest
- For each group, returns the latest date or datetime value. For example, if a group has a record with values 15-12-2014 and 24-12-2014 in a field, then the latest value for the group is 24-12-2014.
- Earliest
- For each group, returns the earliest date or datetime value. For example, if a group has a record with values 15-12-2014 and 24-12-2014 in a field, then the earliest value for the group will be 15-12-2014.