At this point, when the reader is familiar with the principles of the Portfolio definition, we can expand on the subject of the Aggregated Condition that we briefly touched upon in the Logical Conditions section.
The aggregated condition allows setting up criteria for combining several values from a calculated field into a sum and then comparing this number with a preset threshold value to determine whether the records will be selected or discarded by the system during the Portfolio execution.
Reverse Data Processing
It is important to point out, that when the Aggregated Condition is used, the system changes its data processing sequence. Usually, the data is processed from top to bottom of the Portfolio structure. However, when it encounters a node with Aggregated condition, it starts processing data of its children nodes first and then applies Aggregated condition to the resulting data set.
Aggregated Condition Pane
The Aggregated Condition window (Fig.) consists of the following components:
Calculated Field Selection
The Calculated Field selection for this Condition is done with the button that opens a lookup listing available fields. The numbers in this calculated field will be summarized by the system and evaluated against the Comparative value.
Logical Operator Selection
See the Condition Table for the list of Logical Operators associated with the Aggregated condition
Into this input field, you manually enter the number against which you want the system to evaluate the results of the calculations.
Function selection allows you to define the computing procedure you want the system to perform. Pressing the arrow button a menu (Fig.) listing available options.
1. SUMMARY – If SUM is selected, the system will summarize the values in the calculated field and compare the sum against the comparative Value.
2. AVERAGE – If Average is selected, the system will calculate the average of the calculated field values and compare the result against the comparative value.
3. COUNT – If this function is selected, the system will count how many records are present in the group (see grouping criteria) and compare the result against the comparative value.
4. COUNT_DISTINCT – If this function is selected, the system will count how many distinct values of the calculated field are present in the group and compare the result against the comparative value.
5. MINIMUM – If this function is selected the system will determine the Minimum value of the calculated field and compare it against the comparative value.
6. MAXIMUM – If this function is selected the system will determine the maximum value of the calculated field and compare it against the comparative value. Grouping Criteria
You can use Group By button to select transaction attributes the system will use for data compartmentalization. Pressing this button opens a lookup with the list of available transaction attributes.
Aggregated Condition Setup
Let us assume we have a source that consists of 35 transactions. In our portfolio setup, we want to include a segment that contains transactions for counterparties cp_a, cp_b, and cp_c, and only if the Summary value of these transactions equals or exceeds 1 million USD.
To accomplish this, you set up a group of nodes:
1. A parent node with the Aggregated condition specifying, which calculated field to summate (purchase_amount), grouping criteria (counterparty), which function to perform (SUM), which logical operator to apply (>=) and the Comparative value (1,000,000).
2. A child node with a List condition in which you specify which counterparties you want the system to isolate in this portion of the portfolio (counterparties cp_a, cp_b, and cp_c). Alternatively, you can create three child nodes – a node per counterparty in question.
Note: If you do not specify the counterparties and only set up the node with the Aggregated Condition as described above, the system will check every counterparty included in the source.
To set up the Aggregated condition:
1. On the Portfolio panel highlight the node you want to have the Aggregated condition.
2. Click on the Insert Condition option of the Edit pull-down menu and select the Aggregated condition from the list. Blank Aggregated Condition window will appear (Fig.).
3. Press button to open the lookup, listing available calculated fields.
4. Select a calculated field (purchase_amount, in our example) and click OK to close the lookup. The name of the selected field is displayed on the Field selection button (Fig.).
5. In the Function selector, the SUM option is selected by default.
6. Use the Logical Operator selector’s arrow button to select >= option from the list-menu (Fig.).
7. Click on the Comparative Value input field and type in 1000000 (Fig.).
8. Click on the button. The multiple selection lookup opens listing available columns that can be used as transaction attributes.
9. Select a column name and click OK to close the lookup. The selected column name appears in the data grid of the Aggregated Condition window (Fig.).
Aggregated Condition Execution
As you remember, our sample Aggregated condition setup called for three counterparties (cp_a, cp_b, and cp_c). Transactions involving these counterparties were separated during execution by the List condition of the node which was set up for this purpose as a child to the Aggregated condition node.
At this point, the system has not gotten to the Aggregated condition yet and only retrieved transactions based on the conditions in the child node.
As the next step, the system applies the Aggregated condition to the data set resulting from the child node’s condition execution. All isolated transactions are divided into 3 subsegments based on counterparty_name as a grouping criterion. In each group, the system summates amounts in the selected calculated field and checks if the figure in this column is equal to or greater than 1 million. If it is, then all transactions involving this counterparty are accepted.
Multiple Grouping Criteria
You can refine the Aggregated condition setup by selecting several grouping criteria. To expand our example, let us assume that for the selected counterparties cp_a, cp_b, and cp_c you would want to isolate only transactions that were conducted in USD and CD.
To accomplish this you would include currency selection criteria in the child node and then include Currency ID into the data grid of the Aggregated Condition window. The system then would create groupings for every counterparty/currency combination and evaluate the results with our comparative value of 1,000,000.
If you want to add specific locations to your Aggregated Condition setup following the above steps, the system will create all possible counterparty/currency/location combinations and so on.
When you are applying business rules (the combination of logical conditions defined as a portfolio) to a set of transactions (a source), whether institution-wide or department-wide, you must expect your Portfolio to distribute them all among its segments and subsegments.
Quite often, however, some transactions, for one reason or another, do not fall under any category stipulated in your Portfolio definition. We call these transactions ‘orphans’. Creating an Orphans node provides a place for these “loose” transactions to go to.
After you execute your portfolio, you review the results and if the Orphans node has transactions in it you know you need to make adjustments to these transactions to force them to where they are supposed to be (please refer to the Manual Adjustments section of this document for a complete discussion and further instructions).
The Orphan node should contain a logical condition, which will always be satisfied so that all unplaced transactions do not get lost but fall into the Orphans slot.
The specific coding of the condition for the Orphans node depends on your data. For example, if the Book codes are numeric, you could use an Expression condition where you indicate book_code is not equal to zero, which is always true since every transaction in the source must have a book_code. Therefore, left out transactions will be placed within the Orphans node.
Note: You can create an Orphans node at any level of your Portfolio
Note: The system processes nodes in numerical/alphabetical order; therefore we advise you to assign your Orphans node a name starting with one or several letters “Z” to ensure its placement it in the bottom of the portfolio hierarchy.