Rollup Fields
- Becky Weinberg (Unlicensed)
- Pamela Totas (Deactivated)
- Jacob Mathews (Deactivated)
Rollup Fields
A rollup field is a custom field. It performs an aggregation on database fields and detail calculation fields. The most common aggregations include sum, mean, max, min, and weighted average. Rollup fields display values at the security and rollup levels. For example, you can rollup the market value by performing a sum aggregation on the field. As a result, subtotals of each group sum up the individual security market values in that group and display that value at the rollup level.
Rollup fields are different than regular fields, because regular field attributes display values for each security. However, no values are displayed at the grouping levels. Rollup fields display values at the grouping, or rollup levels.
If you do not have permission to create field attributes, this option does not appear. You can not use the custom fields functions without access to field attributes.
Add a Rollup Function to a Field Attribute
Complete the following procedure to add a rollup function to a field attribute.
Select the cell corresponding to the Rollup Field row in the workspace.
You see the Report workspace.Select the field attribute on which you want to perform the aggregation, and select the type of rollup from the available functions on the left.
The right pane of the Rollup Field window displays the existing rollup fields using the selected function on that specific field attribute.To add an existing rollup field to a report, double-click the report.
Complete the following fields.
Option | Description |
Contribution Percentage | Defines the percentage of the total to which this field contributes. |
Count | Counts the number of records based on a selected field and rolls it up to the highest level. |
Count Distinct | This calculation provides you with an easier way to determine the number of unique observations in a sample. For example, you can define a count distinct calculation on the investment type field to determine in how many separate investment types a portfolio is invested. The count distinct calculation operates like the same command in SQL query tools. The calculation retrieves the number of unique observations in a sample. For example, a count distinct value of 1, 5 and 8 is 3. A count distinct value of 1, 3, 3, 5 and 8 is 4. |
Group Percentage Fields | The Group Percentage rollup is closely related to the percentage field. The difference is, when more than one grouping level is used, instead of relating the percentage of a security to the highest level of the report, the securities are broken down to a percentage of 100 in their own grouping level. Null is displayed for all securities within a group when that group level value is (0.00). |
Harmonic Mean Fields | To obtain the harmonic mean, first take the reciprocal of each number, next take the usual average, then take the reciprocal again (because reciprocal is the same as un-reciprocal; therefore, the operation is its own inverse). For example, to find the harmonic mean of 10 and 20, first take 1/10 and 1/20, find their average, which is 3/40, and then take the reciprocal of that, 40/3. In algebra, the harmonic mean H of two numbers A and B is: 1 / ( (1/A + 1/B) / 2) or: 1/m = 1/2 (1/A + 1/B) |
Maximum | The Maximum rollup field takes the highest value being populated in the selected field and rolls that value to the highest grouping level. For example, the rollup field is defined as maximum (Market Value). The highest market value is displayed at the highest grouping level |
Mean Fields | The mean rollup field takes the average of all of the records in the selected fields and then rolls that value up to the highest level. The following example takes the mean of market value. |
Median Fields | The Median rollup field is an alternative to the mean calculation. The median is the middle number when records are arranged in ascending or descending order. For example, the median of 1, 5, and 8 is 5. When there is an even number of records, the median is calculated as the mean of the two middle numbers when the records are arranged in ascending or descending order. For example, the median of 1, 5, 8 and 14 is (5+8)/2 = 6.5. |
Minimum Fields | The Minimum field takes the lowest value in the selected field and rolls it up to the highest level. |
Percentage Fields | The Percentage Rollup field calculates the percentage of each security based on a selected field and rolls it up to the specified level. In the example below, the percentage is based on the market value field. |
Rank (Ascending) | Ranks the numeric values of single and multiple nodes within a fund, a group (COMPs, ACOMs, COMP of COMPs, ACOM of ACOMs) whereby the members are ranked, and within a multi-level Entity Hierarchy, in ascending order, treating the lowest value first and the highest value last in the series. |
Rank (Descending) | Ranks the numeric values of single and multiple nodes within a fund, a group (COMPs, ACOMs, COMP of COMPs, ACOM of ACOMs) whereby the members are ranked, and within a multi-level Entity Hierarchy, in descending order, treating the highest value first and the lowest value last in the series |
Ratio Fields | Two fields are required. The following example uses Base Value and Second Value. The report ratio is determined by the following function:
|
Report Ratio Fields | This calculation is based on three fields: Ratio (Base Value, Second Value, Third Value) For example, the three fields used Market Value, Cost, and Shares. Their values are: MV = $450,000 Cost = $400,000 Shares = 1,000 The calculation being performed is: Base Value / Second Value * Third Value. In the example it would be Market Value / Cost * Shares, or: 450,000 / 400,000 * 1,000 = 1,125 So the resulting ratio value would be 1,125. |
Standard Deviation Fields | The standard deviation is a statistic that tells how tightly all the various examples are clustered around the mean in a set of data. When the examples are tightly bunched together and the bell-shaped curve is steep, the standard deviation is small. When the examples are spread apart and the bell curve is relatively flat, there is a relatively large standard deviation. The standard deviation for population in this example is 14.14. The calculation is as follows: Calculate the mean value of 50, 40, 30, 20, and 10. That is: (50+40+30+20+10) / 5 = 30 Population i.e. number of values used in the calculation of standard deviation = 5. Standard Deviation for population is: Square Root ( (50-30)2 + (40-30)2 + (30-30)2 + (20-30)2 + (10-30)2 / 5) = 14.14 |
Standard Deviation for Population | Similar to Standard deviation except that instead of working with a complete set of numbers, you only have a sample. N-1 is used for a more conservative estimate especially with smaller samples. As the size of the sample increases, N-1 has less effect on the results. |
Sum Fields | The Sum rollup field adds the values of each security from the field that the rollup was based on and rolls the value to the specified level. |
Weighted Average Fields | Weighted Average is a calculation using a field based on another field. For example, you may want to see what the weighted average of a price compared to the quantity held of that security and roll it up to the portfolio level. |
Use Rollup Fields to Group Securities by Quintile, Quartile, and Decile
Use the NTile Rollup field to rank data in range categories, including quintiles, quartiles, deciles, and percentiles. This is useful when analyzing data, such as ranking P/E ratio in quintiles.
For example, you want to create a report with securities grouped into quartile subsets. First, you create a new rollup field called Quartile Rank via the Rollup Field dialog box. Input parameters include:
Calculations
Based on Field
Subset
Subset Value
Rank Order
The rollup field uses the value in the Based on Field field and ranks it using a “competition” ranking algorithm.
Next, you use the Quartile Rank field to create a range grouping rule, which is then used to create the report.
Create a Quartile Grouping
The following procedure describes how to create a quartile grouping. You can use the same procedure to create a quintile, decile, or percentile grouping.
From any Eagle window, click the Eagle Navigator button to access the Eagle Navigator.
Enter Field Attributes in the Start Search text box. click the Field Attributes (System Management Center) link to access the Field Attributes window.
You see the Field Attributes window.Click the Custom folder, and then the Rollup folder.
Click New.
You see the Rollup Field dialog box.In the Calculations field, enter NTILE. You specify the aggregation type in the Calculations field. After you enter NTILE, additional fields appear in the Rollup Fields dialog box.
Complete the following fields, and then click OK.
Option | Description |
|---|---|
Based On Field. | The field you want to rank. |
Subset | The rank type. To create a quartile grouping, enter Quartile. |
Rank Order | The rank order, which is greatest or least equals highest ranks. |
Now, create a new range grouping rule as explained in the following procedure.
Create a New Range Grouping Rule
You create a new range grouping rule using the rollup field you created in the previous procedure.
From the General Reporting module, select the report type you want to use in the Grouping Rules folder in tree view.
Right-click and select New.
You see the New Range Grouping Rule dialog box.In the Name field, enter a name for the rule.
In the Comments field, enter an optional comment about the rule.
Click to populate the field details with the Quartile Rank field.
Double-click the text box under Range Title, and enter Rank 1
Double-click the text box under Range, select the = operator and enter the value 1.
Repeat steps 6 and 7 for the remaining three ranks, Rank 2, Rank 3, and Rank 4.
You see the Edit Range Grouping dialog box displaying the new range grouping rule.
The following example shows a sample report with range grouping in place.