You can use the COLLECT() function to return a result when a line item subset is used as a dimension. A line item subset is a selection of line items from one or more modules. It has all the properties of a normal list and can be:
- reused in other modules; or
- used in a list-formatted line item to create a drop-down list.
The function collects the original line item values from the source module or modules that you selected for your line item subset.
Syntax
COLLECT()
Format
| Input Format | Output Format |
|---|---|
| Number | Number |
Arguments
The function requires no arguments.
Constraints
The function has the following constraints:
- Parameters: Can only be used as a stand-alone function taking no parameters and always appearing as COLLECT().
- Usage: Cannot be used as part of a conditional formula.
- Source Module: The source module or modules must contain the original line items that were selected for the line item subset used in the result module.
- Result Module: The result module must have a line item subset as a dimension.
Excel equivalent
- No Excel equivalent
Example
In this example, a line item subset has been added to the model:
The Receipts and Payments line item subsets has original items selected from the P&L, Gross Margin, and Fixed Assets modules.
In the result module, the line item subset is used as a dimension:
The COLLECT() function is used against the line item subset items as part of the cell formulas:
COLLECT() * 'Inc/Expse Flag'.'Inc/Expse Flag'
The formula uses COLLECT() to collect the original data values from the P&L and Fixed Assets source modules and then multiplies collected values by a plus or minus flag (for either income value or cost value), where the flag value is drawn from another source module:
And in this example, the original Sales item value from the P&L module for London in Jan '11 of 105,000 has been multiplied by a positive flag to be cast as an income value. In contrast, Staff Costs, after collection from the Gross Margin module, is multiplied by a negative flag and so is cast as a cost value.